I spent a lot of time on this one, and it wasn't clear from examples precisely how the various items should be used together.
The format of the ADMIN_CMD query is pretty straight-forward, and in our case, it took on roughly this form (edited to remove any identifying data):
______________________
CALL SYSPROC.ADMIN_CMD('EXPORT TO /datadestination/subfolder1/subfolder2/FileName.IXF OF IXF MESSAGES ON SERVER SELECT ');
______________________
Using ADMIN_CMD from CLP
If you're using ADMIN_CMD from a normal query, then it's pretty straight-forward. The command will return a result set that has a query that gets you more detailed information about what you just did.
For example:
NOTE: Result set is edited to remove whitespace.
Result set 1
--------------
ROWS_EXPORTED MSG_RETRIEVAL MSG_REMOVAL
-------------------- ----------------------------------------------------------------------------------- ----------------------------------------------------
0 SELECT SQLCODE, MSG FROM TABLE(SYSPROC.ADMIN_GET_MSGS('28138_DB2ADMIN')) AS MSG CALL SYSPROC.ADMIN_REMOVE_MSGS('28138_DB2ADMIN')
1 record(s) selected.
Return Status = 0
To break that out a bit, the MSG_RETREIVAL query is:
MSG_RETRIEVAL
-----------------------------------------------------------------------------------
SELECT SQLCODE, MSG FROM TABLE(SYSPROC.ADMIN_GET_MSGS('28138_DB2ADMIN')) AS MSG
... The MSG_REMOVAL query is:
MSG_REMOVAL
-------------------------------------------------
CALL SYSPROC.ADMIN_REMOVE_MSGS('28138_DB2ADMIN')
You can then just copy and paste those, enter them into a query window and get results.
Using ADMIN_CMD from a stored procedure
This gets a bit more dodgy.
The problem, of course, on DB2 LUW 9.5, there's just not a great, straight-forward way to retrieve these results.
Fortunately, I was able to find an excellent example on dbforums, submitted by db2girl.
Example can be found
here
DB2Girl's profile can be found
here.
Turns out, it is necessary to create a RESULT_SET_LOCATOR that we associate with the call to ADMIN_CMD(Export). I'm a bit embarrassed to admit that I had not previously heard of a RESULT_SET_LOCATOR.
Even now, I'm finding it hard to find documentation on this. Prior to DB2 10, it seems to mostly appear in 3rd party documentation, such as that from Micro focus.
I guess I'm not surprised by this, IBM. But, really? Not one little example of this? Maybe it's in the COBOL documentation. I dunno.
So, what I place here is "by rote", meaning I don't fully understand it.
I can deduce from the example below the following:
- RESTULT_SET_LOCATOR is a DB2 Data Type
- It can be "associated" with a command using the ASSOCIATE RESULT SET LOCATORS command.
- A cursor can then be allocated against the result set, and used to retrieve data.
What I don't know (and what bothers me):
- VARYING is obviously a modifier. What, precisely, does it mean, and what alternatives are available?
The approach I took was to simply create a table for logging and inserted the results of the call to SYSPROC.ADMIN_CMD into that table. If all is not well after the job runs, this can be queried.
A better approach would be to get these results and log them, then clean up the results using ADMIN_REMOVE_MSGS().
CREATE PROCEDURE RATESTUDYSR.SP_SQL_EXP_PSH_RS_SEG_LOAD (IN INT_P_BEGIN_DATE INT, IN INT_P_END_DATE INT)
DYNAMIC RESULT SETS 1
P1: BEGIN
DECLARE V_MSG_RETREIVAL VARCHAR(512);
DECLARE V_ROWS_EXP BIGINT;
DECLARE V_MSG_REMOVAL VARCHAR(512);
DECLARE RESULT1 RESULT_SET_LOCATOR VARYING;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND BEGIN END;
CALL SYSPROC.ADMIN_CMD('EXPORT TO /export.ixf OF IXF MESSAGES ON SERVER SELECT Manyfields FROM mytables JOIN othertables' );
ASSOCIATE RESULT SET LOCATORS(RESULT1) WITH PROCEDURE SYSPROC.ADMIN_CMD;
ALLOCATE RSCUR CURSOR FOR RESULT SET RESULT1;
FETCH RSCUR INTO V_ROWS_EXP, V_MSG_RETREIVAL, V_MSG_REMOVAL;
INSERT INTO RATESTUDYSR.LOG_SP_SQL_EXP_PSH_RS_SEG_LOAD VALUES (CURRENT_TIMESTAMP, 'TO RETREIVE ADMIN_CMD INFO: ' || V_MSG_RETREIVAL);
END P1
So, that's where I'm at as of now. It's not hard to imagine retuning the result set from the stored procedure rather than logging it, but that's work for another day ;-)
