Example: Run CL commands that use SQL stored procedures and ODBC

Stored procedure support provides a means to run IBM® i Control Language (CL) commands by using the SQL CALL statement.

Use CL commands when:

Performing an override for files
Initiating debug
Using other commands that can affect the performance of subsequent SQL statements
Doing other environmental setup for an application

The following examples show cases where an IBM i CL command is run by using the CALL statement which calls the program designed for running CL commands from SQL. That program (QCMDEXC in library QSYS2) expects two parameters:

A string that contains the command text to execute
An integer that contains the length of the command text

The parameters must include these attributes for the command to be interpreted properly.

In the following example, a C program on the PC is going to run an OVRDBF command that is 65 characters long (including embedded blanks). The text of the OVRDBF command is as follows:

 OVRDBF FILE(TESTER) TOFILE(JMBLIB/TESTER) MBR(NO2) OVRSCOPE(*JOB)

The code for performing this command by using ODBC APIs is as follows:

 HSTMT hstmt;
SQLCHAR stmt[301];

rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
strcpy(stmt,"CALL QSYS2.QCMDEXC('OVRDBF FILE(TESTER) TOFILE(MYLIB/");
strcat(stmt,"TESTER) MBR(NO2) OVRSCOPE(*JOB)',64)");
rc = SQLExecDirect(hstmt, stmt, SQL_NTS);

Statements now run against file MYLIB/TESTER will reference member NO2 rather than the first member.

Another CL command that is useful to run against a database server job is the STRDBG command. You do not have to call a stored procedure to run this command, though. There is an option on the Diagnostic tab of the DSN setup GUI on the Diagnostic tab that will automatically run the STRDBG command during the connection attempt.