Stored Procedures and DB2

Have you ever wanted to issue a DB2 command from your program? It's actually possible, thanks to stored procedures. Procedure SYSPROC.ADMIN_COMMAND_DB2 allows you to issue DB2 commands. Information on this and other administrative stored procedures is available in the Administration Guide. For the different versions of DB2 manuals, go here.

Unfortunately, IBM doesn't provide a simple way of calling a stored procedure from TSO using SPUFI or some other command line mode. However, you can test the stored procedure from your client if you have DB2 Connect installed or Data Studio with a license to connect to z/OS.
To determine which databases and tablespaces are in a restrictive state, issue this from the DB2 command line:

call sysproc.admin_command_db2('-dis db(*) sp(*) restrict limit(*)', 34,TS,null,?,?,?,?,?,?,?,?)

(Note: The third parameter, TS, instructs the stored procedure to parse the string in parameter 1 as a  –DISPLAY DATABASE (*) SPACENAM(*) statement and return table space information.) 

To learn which IBM administrative procedures are installed, query the DB2 catalog. The following query can be used to list all install procedures owned by SYSPROC, which is usually the schema owner for these procedures:

SELECT SUBSTR(SCHEMA,1,8) AS SCHEMA
           , SUBSTR(NAME,1,18) AS NAME
 FROM SYSIBM.SYSROUTINES
WHERE SCHEMA = 'SYSPROC'
ORDER BY SCHEMA, NAME

If you use these procedures in your application, please respond in Comments.

Comments

[Mark]

The TS parameter needs to be in quotes.

call sysproc.admin_command_db2('-dis db(*) sp(*) restrict limit(*)', 34,'TS',null,?,?,?,?,?,?,?,?)

[Abhay]

Has anyone tried this?

My command line editor or DB2CLP abends and returns nothing

[Paul Schulz]

No luck for me either - I get the return parameters back but no output. I am pretty sure the CMD_EXEC should be = 1 if it actually worked (based on successful execution through IBM Data Studio):
db2 => call sysproc.admin_command_db2('-dis db(*) sp(*) restrict limit(*)', 34,TS,null,?,?,?,?,?,?,?,?)
CMD_EXEC: 0
IFCA_RET: 4
IFCA_RES: 15075332
XS_BYTES: 0
IFCA_GRES: 15075350
GXS_BYTES: 0
RETURN_CODE: 0
MSG: NULL

"ADMIN_COMMAND_DB2" RETURN_STATUS: 0

db2 =>

[Paul Schulz]

It turns out that null does not work for MEMBER (in my environment). I had to enter a correct Data Sharing Member name and it started to work properly - I get all the results back including CMD_EXEC = 1 (and all the rest of the return parms are = 0.

[Robert Plata]

The IBM Info Center documentation for this procedure states:
"If you specify processing-type, you must specify the command name in full, such as "-DISPLAY THREAD". You cannot abbreviate DB2 commands, such as "-DIS THD"." Troy uses a processing type of 'TS' in his example. I tried the example in my CLP, and it worked. But, I used: CALL SYSPROC.ADMIN_COMMAND_DB2('-DISPLAY DATABASE(*) SPACENAM(*) RESTRICT LIMIT(*)',100,'TS',NULL,?,?,?,?,?,?,?,?).

HTH. Bob