When using the COLLIST keyword in a SQL SELECT statement via a CA OPS/MVS OPS/REXX program, why the documented variables does not return any value?

Document ID : KB000022309
Last Modified Date : 14/02/2018
Show Technical Document Details

Question:

When using the COLLIST keyword in a SQL SELECT statement via a CA OPS/MVS OPS/REXX program, why the documented variables does not return any value?

Answer:

The OPS/REXX code requires the SYSTEM(*) keyword in order to return output in the output variables.

Here is a sample REXX:

ADDRESS 'SQL' "SELECT * FROM STCTBL SYSTEM(*) COLLIST" <=== Add SYSTEM(*) Keyword   
SAY SQLCODE   
SAY COLUMN#KEYS   
SAY COLUMN#LIST   
SAY COLUMN#TYPE  

And this is a sample of the output returned:

 0                                                                              
 NAME                                                                          
 NAME CURRENT_STATE DESIRED_STATE MODE PREMODE REFMODE ACTMODE SCHEDMODE JOBNAME
 TYPE CHKPOINT_STATE IPL_STATE TNGNOTIFY RESOURCE_TEXT PREREQ MISSING_PREREQ PRE
V_STATE RESACT_COUNT SSMMONITOREDCOL1 SSMMONITOREDCOL2 SSMMONITOREDCOL3 SSMMONIT
ORED                                                                           
 1 1 1 1 1 1 1 1 1 1 1 1 1 2 2 2 1 4 4 2 1 4 1 1 9 3                           
 ***                                                                           

Additional Information:

You can find additional information at our website: CA Technologies Documentation