Thursday, November 26, 2020

Run CL commands from SQL - IBM i

QCMDEXC (SQL Procedure):

On IBM i, CL (Control Language) has always been the best choice for writing the programs with the need for running lot of commands. 

Occasionally we get the need to run these commands from outside CL Programs (like RPG). Earlier I wrote about how to run these commands from RPGLE using QCMDEXC. Click Here to see how to run CL commands in RPGLE. 

With the introduction of QCMDEXC procedure, This can now be done in SQL.

This is a very useful addition while working with SQL Procedures, Functions, Triggers etc... 

There are various CL commands that can be run using QCMDEXC. First thing that comes to my mind is adding or removing a library from library list. 


If we are running SQL Queries either using STRSQL from 5250 session or using Run SQL scripts from IBM i ACS, 

If we need to add or remove libraries, we either need to add or remove libraries outside of SQL (for 5250) or amend JDBC configuration (Run SQL Scripts).

With this, we could simply call 'QCMDEXC' (this procedure is present in QSYS2 library) and pass the required command as parameter. 

In our example, this would be 'ADDLIBLE' command. 



This can be run inside SQL procedure either by passing the command directly or by preparing the command into a variable and passing variable as parameter. 


We will take the same example of ADDLIBLE, But will prepare the command in a variable and pass variable as Parameter. 

QCMDEXC in SQL Procedure

In the above Example, 
  • Lines 3 & 4 - Declaring the variables to store Library and Command to be Executed in a Stored Procedure.
  • Line 6 - Assigning the Library name to variable. 
  • Line 8 - Concatenating the Library name to 'ADDLIBLE' and assigning the final command to variable. 
  • Line 10 - Pass Variable 'CMD_TO_EXEC' as parameter to QCMDEXC.
Alternatively, We do not need to prepare the full command always before calling the procedure. This can be concatenated and passed as parameter directly. 

QCMDEXC in SQL Procedure

In the above example, 
  • In Line 7 - We are directly concatenating Library name and passing it as parameter to QCMDEXC. 

If you have any Suggestions or Feedback, Please leave a comment below or use Contact Form.

No comments:

Post a Comment

Different Ways of Sorting Data in a List - Python

Sorting Data in a List List is a collection of data (of different data types), much like an array. Like any data structure or data set, dat...