SQL Tool Utility

Edit online

Use SQL Tool Utility to generate SQL scripts that are required to create data sources and Client Item trees.

Before you begin

The SQL Tool Utility consist of the following files:
  • ACMfgCommandLineSQLTool.jar
  • mapping.json
  • ReadMe.txt
    Note: Make sure that you go through the instructions in the ReadMe.txt file.
Attention:
  1. Refer to the mapping.json, the sample file provided as reference for creating the data sources and Client Items for each of the data source. Multiple Client Items can be added to one data source.
  2. Every Client Item in the mapping.json file comes with source_item_addr attribute that denotes the nodeId along with its mapping path from the Toolkit.
  3. You need to update the mapping.json file based on your Client Items and OPC server data configuration. The structure of the mapping file must remain unchanged.
  4. Do not change the values of the attributes message_security_mode (None) and security_policy ( http://opcfoundation.org/UA/SecurityPolicy#None) in the mapping.json file.
  5. The Utility always creates a new node for the mapping_path that is provided in the mapping.json file.

    Ensure that you provide a new complete mapping path for both the data source and Client Item before running the Utility.

  6. The discovery_endpoint property is used for adding the discovery URL.

About this task

Use the SQL Tool Utility to generate SQL script, which is required to create data sources and Client Item trees in the App Connect for Manufacturing. Use the SQL script to run on the database that is used in the Toolkit. The data then gets reflected in the Toolkit.

Procedure

  1. Navigate to the ACMfgCommandLineSQLTool contents directory.

    For example,

    UnixLinux opt/IBM/ACMfg- 3.0.1.n /runtime/amd64_linux_2\setup\ACMfgCommandLineSQLTool

    Windows C:\Program Files\IBM\ACMfg\ 3.0.1.n \runtime\amd64_nt_4\setup\ACMfgCommandLineSQLTool

    The SQL Tool Utility consist of the following files:
    • ACMfgCommandLineSQLTool.jar
    • mapping.json
      • Sample file that has a list of input data sources and Client Items. This file is used as the input for the SQL Tool Utility.
      • You need to update this file based on your OPC server data configuration before you use the SQL Tool Utility.
      • Ensure that you provide the source_item_addr value in mapping.json file to create the Client Item tree.
    • ReadMe.txt

      Make sure that you go through the instructions in the ReadMe.txt file.

  2. Update the mapping.json based on your OPC server data configuration and save the file to a location of your choice.
  3. Open a command prompt window and run the following command: 
    java -jar ACMfgCommandLineSQLTool.jar <mapping.json_file_folder_path>
    Note: You can place the mapping.json file in any directory.

    In this example, the updated mapping.json file is saved at D:\backup\SQL-scripts.

    For example,
    java -jar ACMfgCommandLineJsonTool.jar D:\backup\SQL-scripts

    An output.txt file is generated in the same folder where the mapping.json file is present.

    In this example, the output.txt is generated in D:\backup\SQL-scripts folder. The output.txt file consists of the SQL scripts that are required to create data sources and Client Item tree.

  4. Take a backup of your existing database that is used for App Connect for Manufacturing applications.
  5. Run the output.txt scripts on your database.

What to do next

Restart the Toolkit and navigate to Manufacturing Perspective. You can view the data sources and Client Item tree.