Troubleshooting
Problem
This document provides a sample CL program to run the RUNSQLSTM command.
Resolving The Problem
The following is a sample CL program to run SQL scripts via the CL command RUNSQLSTM. Do the following:
Step 1: Create a source physical file called MYLIB/SOURCE by typing the following:
CRTSRCPF FILE(MYLIB/SOURCE)
Press the Enter key.
Step 2: Using PDM, create a text member (for example, MYSQLCMD) within the source physical file MYLIB/SOURCE that has the following SQL statements:
*************** Beginning of data ****************************************************************
0001.00 INSERT INTO MYLIB/MYFILE VALUES(23, 32, 'ssc'); 070926
0002.00 INSERT INTO MYLIB/MYFILE VALUES(23, 32, 'EDU'); 070926
0003.00 INSERT INTO MYLIB/MYFILE VALUES(23, 32, 'PMP'); 070926
0004.00 INSERT INTO MYLIB/MYFILE VALUES(23, 32, 'BAR'); 070926
****************** End of data *******************************************************************
Step 3: Using PDM, create a CLP member (for example, RUNSQL) within the source physical file MYLIB/SOURCE with the following:
*************** Beginning of data *****************************************************************
PGM 070926
RUNSQLSTM SRCFILE(MYLIB/SOURCE) SRCMBR(MYSQLCMD) + 070926
COMMIT(*NONE) OUTPUT(*PRINT) 070926
ENDPGM 070926
****************** End of data ********************************************************************
Step 4: Compile the above CL program.
Step 5: Invoke the program using CALL MYLIB/RUNSQL.
Step 6: When you run the above program, it generates a spooled file because the OUTPUT(*PRINT) option is used with RUNSQLSTM. The spooled file shows that the SQL statements ran successfully.
Note: Remember that you cannot use the SQL command SELECT with RUNSQLSTM.
Step 1: Create a source physical file called MYLIB/SOURCE by typing the following:
CRTSRCPF FILE(MYLIB/SOURCE)
Press the Enter key.
Step 2: Using PDM, create a text member (for example, MYSQLCMD) within the source physical file MYLIB/SOURCE that has the following SQL statements:
*************** Beginning of data ****************************************************************
0001.00 INSERT INTO MYLIB/MYFILE VALUES(23, 32, 'ssc'); 070926
0002.00 INSERT INTO MYLIB/MYFILE VALUES(23, 32, 'EDU'); 070926
0003.00 INSERT INTO MYLIB/MYFILE VALUES(23, 32, 'PMP'); 070926
0004.00 INSERT INTO MYLIB/MYFILE VALUES(23, 32, 'BAR'); 070926
****************** End of data *******************************************************************
Step 3: Using PDM, create a CLP member (for example, RUNSQL) within the source physical file MYLIB/SOURCE with the following:
*************** Beginning of data *****************************************************************
PGM 070926
RUNSQLSTM SRCFILE(MYLIB/SOURCE) SRCMBR(MYSQLCMD) + 070926
COMMIT(*NONE) OUTPUT(*PRINT) 070926
ENDPGM 070926
****************** End of data ********************************************************************
Step 4: Compile the above CL program.
Step 5: Invoke the program using CALL MYLIB/RUNSQL.
Step 6: When you run the above program, it generates a spooled file because the OUTPUT(*PRINT) option is used with RUNSQLSTM. The spooled file shows that the SQL statements ran successfully.
Note: Remember that you cannot use the SQL command SELECT with RUNSQLSTM.
Note: ACS Run SQL Scripts output to a source physical file may not work with RUNSQLSTM, due to the formatting choices that ACS Run SQL Scripts makes.
[{"Type":"MASTER","Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000001i4eAAA","label":"IBM i Db2-\u003ESQL Examples \/ DB Examples \/ Misc how to"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions"}]
Historical Number
464585542
Was this topic helpful?
Document Information
More support for:
IBM i
Component:
IBM i Db2->SQL Examples / DB Examples / Misc how to
Software version:
All Versions
Operating system(s):
IBM i
Document number:
636257
Modified date:
26 November 2024
UID
nas8N1014086
Manage My Notification Subscriptions