Using SPUFI to run SQL commands

1. From the main screen, click Apps.
2. From the Diagnose/Explore box, click SPUFI. The following panel is displayed.
3. Enter the connection details as described below:
   1. ODBM (IMS Connect)—Select the host LPAR and DRDA port that you want to connect to.
   2. Alias—Select the datastore alias for the IMS system from the available aliases connected to the selected ODBM.
   3. PSB—Select the name of the PSB to connect and submit SQL queries against.
   4. Password—Password for the current logged in user OR the MFA token, if the user is an MFA enabled user.
4. Once connected, you can use one of the following tabs:
   • SQL Editor tab—You can manually type in the SQL query and click Submit to execute the query.
   • SQL Generator tab—You can generate a valid query by selecting available tables (segments) and columns (fields). You can copy the generated query to the SQL editor page and execute it.
   • History tab—You can save recently executed queries and even edit and execute up to 50 saved queries.
5. After following one of the steps mentioned in step 4, you can view the result of the executed queries in the dynamically added result tab along with the executed query details.
   • Along with seeing the result of the executed query, you can export the individual results in a CSV format.
   • If any of the executed queries results in an error, the details of the error explaining the issue are added at the bottom of the SQL editor and the History tabs.
   • In the event of an IMS Connect timeout, the ICM server will try to reconnect automatically. If it cannot, for instance, due to MFA single-use tokens, a prompt will appear to ask for updated credentials to reconnect. Once connected, the submitted query will get re-executed with a new connection.

6. Optionally, select the History tab to carry out the following operations:

   • Use the Edit query criteria option to edit and run the selected query or edit and update the selected query.
   • Edit and execute queries using the Recently executed query option.
   • Delete a specific query or even clear all history from the Recently executed query pulldown menu.
   • Save up to a maximum of 50 queries per user using the Recently executed query pulldown menu, and use those saved queries later from the Saved Query pulldown menu.
   • Edit, execute, or delete already saved queries from the Saved Query pulldown menu.

   Important

   Queries in the Recently executed query menu are only available while the tab is open, and as soon as tab is closed the queries will be cleared. In the Saved Query menu, the saved queries persist unless explicitly deleted.

Using the Autocomplete feature from the SQL Generator tab

To use the Autocomplete feature from the SQL Generator tab:

1. Click Load Schema to load the PSB schema from the catalog of the connected PSB.
2. Select a PCB from the available PCB list. It will load the available tables (segments).
3. Select the required table (segment) from the available list to load the available column (fields) of the selected table.
4. You can select multiple fields available in the list of the selected table.
   • On selecting at least one or more columns of the selected table, the Select Table list will get updated to only include the parent segments in the hierarchical path up to the root segment and all its descendant segments.
   • Tags will be added on the Selected fields legend which you can use to remove any fields that are no longer needed.
   • In the Selected fields legend, the tags are added in the order of the hierarchical path and are color-coded the same color for the fields with the same table (segment).
5. Click Copy to editor in the SQL Query legend. This will copy the generated query to the SQL Editor tab.  From there, you can submit the query to see the results.