Enuda
asked on
REORGCHK-REORG Script not working. Can someone tell me what I am doing wrong?
Hi all,
How can I implement REORG using using REORGCHK to tell REORG what DB2 UDB v8, tables, etc to perform REORG on? Specifically,
I want to create a store procedure, build array of candidate UDB tables call REORGCHK_TB_STATS, check F1 thru F3 to qualify
tables for reorg, pass selected (pass candidate tablespaces, perhaps one at a time?) to REORG utility for Reorg action.
Now, I found a script written by a Norm Wong that is promising but I can't seem to get it to run in my environment - UDB
v8.2 installed both in my Windows XP and Red Hat Linux. I reviewed the IBM scripting documentation ie "Application
Development Guide" and the script I am using seem to conform to standards and examples shown but fails to successfuly
execute in my environment. I can't for the life of me figure this out! Please indulge me a second to paste a section of what
I am struggling with viz:
DECLAREs:
DECLARE procresult RESULT_SET_LOCATOR VARYING ;
DECLARE TABLE_SCHEMA VARCHAR(128) ;
DECLARE TABLE_NAME VARCHAR(128) ;
DECLARE CARD INTEGER Default 0;
DECLARE OVERFLOW INTEGER Default 0;
DECLARE NPAGES INTEGER Default 0;
DECLARE FPAGES INTEGER Default 0;
DECLARE ACTIVE_BLOCKS INTEGER Default 0;
DECLARE TSIZE INTEGER Default 0;
DECLARE F1 FLOAT ;
DECLARE F2 FLOAT ;
DECLARE F3 FLOAT ;
DECLARE F4 FLOAT ;
DECLARE F5 FLOAT ;
DECLARE F6 FLOAT ;
DECLARE REORG CHAR(3) ;
DECLARE has_lob INTEGER DEFAULT 0 ;
ERRORS: Each and every one of the above declares returns the following error:
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0104N An unexpected token "END-OF-STATEMENT" was found following "_SET_LOCATOR VARYING". Expected --tokens may include: "<psm_semicolon>". LINE NUMBER=24. SQLSTATE=42601
Please note that the Result_set_locator string appears to be truncated and I can't explain it. Isn't that a reserved word/string?
Could anyone give a hand with this problem? Any help will be highly appreciated.
Thanks
Okonita
How can I implement REORG using using REORGCHK to tell REORG what DB2 UDB v8, tables, etc to perform REORG on? Specifically,
I want to create a store procedure, build array of candidate UDB tables call REORGCHK_TB_STATS, check F1 thru F3 to qualify
tables for reorg, pass selected (pass candidate tablespaces, perhaps one at a time?) to REORG utility for Reorg action.
Now, I found a script written by a Norm Wong that is promising but I can't seem to get it to run in my environment - UDB
v8.2 installed both in my Windows XP and Red Hat Linux. I reviewed the IBM scripting documentation ie "Application
Development Guide" and the script I am using seem to conform to standards and examples shown but fails to successfuly
execute in my environment. I can't for the life of me figure this out! Please indulge me a second to paste a section of what
I am struggling with viz:
DECLAREs:
DECLARE procresult RESULT_SET_LOCATOR VARYING ;
DECLARE TABLE_SCHEMA VARCHAR(128) ;
DECLARE TABLE_NAME VARCHAR(128) ;
DECLARE CARD INTEGER Default 0;
DECLARE OVERFLOW INTEGER Default 0;
DECLARE NPAGES INTEGER Default 0;
DECLARE FPAGES INTEGER Default 0;
DECLARE ACTIVE_BLOCKS INTEGER Default 0;
DECLARE TSIZE INTEGER Default 0;
DECLARE F1 FLOAT ;
DECLARE F2 FLOAT ;
DECLARE F3 FLOAT ;
DECLARE F4 FLOAT ;
DECLARE F5 FLOAT ;
DECLARE F6 FLOAT ;
DECLARE REORG CHAR(3) ;
DECLARE has_lob INTEGER DEFAULT 0 ;
ERRORS: Each and every one of the above declares returns the following error:
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0104N An unexpected token "END-OF-STATEMENT" was found following "_SET_LOCATOR VARYING". Expected --tokens may include: "<psm_semicolon>". LINE NUMBER=24. SQLSTATE=42601
Please note that the Result_set_locator string appears to be truncated and I can't explain it. Isn't that a reserved word/string?
Could anyone give a hand with this problem? Any help will be highly appreciated.
Thanks
Okonita
P1: BEGIN
-- Declare variables
-- Locator variable for result to call to reorg check
--
DECLARE procresult RESULT_SET_LOCATOR VARYING ;
-- Host variables for call to reorg check ------------------
--
DECLARE TABLE_SCHEMA VARCHAR(128) ;
DECLARE TABLE_NAME VARCHAR(128) ;
DECLARE CARD INTEGER Default 0;
DECLARE OVERFLOW INTEGER Default 0;
DECLARE NPAGES INTEGER Default 0;
DECLARE FPAGES INTEGER Default 0;
DECLARE ACTIVE_BLOCKS INTEGER Default 0;
DECLARE TSIZE INTEGER Default 0;
DECLARE F1 FLOAT ;
DECLARE F2 FLOAT ;
DECLARE F3 FLOAT ;
DECLARE REORG CHAR(3) ;
DECLARE has_lob INTEGER DEFAULT 0 ;
-------- Return code from call to reorg check ---------------
DECLARE retval INTEGER DEFAULT 0 ;
--------------- Reorg command string ---------------
DECLARE reorgcmd VARCHAR(512) ;
----------------------------- LOOP variables -------
--
DECLARE at_end SMALLINT DEFAULT 0 ;
DECLARE not_found CONDITION FOR SQLSTATE '02000' ;
DECLARE CONTINUE HANDLER FOR not_found, SQLEXCEPTION, SQLWARNING
SET at_end = 1 ;
DECLARE GLOBAL TEMPORARY TABLE reorgprd1
(TABLE_SCHEMA VARCHAR(128) NOT NULL,
TABLE_NAME VARCHAR(128) NOT NULL)
ON COMMIT PRESERVE ROWS
NOT LOGGED ON ROLLBACK PRESERVE ROWS
WITH REPLACE ;
---------------Process Tablespces ---------------------------
-- Call REORGCHK_TB_STATS for reorg rcommendations ----------------
CALL REORGCHK_TB_STATS('S','DB2ADMIN') ;
GET DIAGNOSTICS retval = DB2_RETURN_STATUS ;
IF retval <> 0 THEN
LEAVE P1 ;
END IF ;
ASSOCIATE RESULT SET LOCATORS (procresult) WITH PROCEDURE REORGCHK_TB_STATS ;
ALLOCATE rsCur CURSOR FOR RESULT SET procresult ;
fetch_loop:
REPEAT
FETCH rsCur INTO TABLE_SCHEMA
,TABLE_NAME
,CARD
,OVERFLOW
,NPAGES
,FPAGES
,ACTIVE_BLOCKS
,TSIZE
,F1
,F2
,F3
,REORG ;
IF (LOCATE('*',REORG) > 0 AND at_end = 0) THEN
---Tables with LOBs cannot be reorged INPLACE ALLOW WRITE ACCESS -------
SET has_lob = 0 ;
SELECT DISTINCT 1 INTO has_lob FROM SYSCAT.COLUMNS COL
WHERE COL.TABSCHEMA = TABLE_SCHEMA AND COL.TABNAME = TABLE_NAME AND TYPENAME LIKE '%LOB%' ;
IF has_lob = 0 THEN
--- Pass candidate objects to DB2 UDB REORG Utility program using ---
--- ADMIN_CMD proc --
SET reorgcmd = 'reorg table "'||RTRIM(TABLE_SCHEMA)||'"."'||RTRIM(TABLE_NAME)||'" INPLACE ALLOW WRITE ACCESS' ;
SET at_end = 0 ;
ELSE
SET reorgcmd = 'reorg table "'||RTRIM(TABLE_SCHEMA)||'"."'||RTRIM(TABLE_NAME)||'"' ;
END IF ;
--
CALL ADMIN_CMD(reorgcmd) ;
GET DIAGNOSTICS retval = DB2_RETURN_STATUS ;
IF retval <> 0 THEN
SIGNAL SQLSTATE '99000'
SET MESSAGE_TEXT = 'Error in reorg call' ;
LEAVE P1 ;
END IF ;
INSERT INTO SESSION.reorgtarg(TABLE_SCHEMA, TABLE_NAME) VALUES(TABLE_SCHEMA, TABLE_NAME) ;
END IF ;
UNTIL at_end > 0
END REPEAT fetch_loop ;
-- Return list of reorged tables to caller --
BEGIN
DECLARE clientcurr CURSOR WITH RETURN TO CALLER
FOR SELECT DISTINCT TABLE_SCHEMA, TABLE_NAME
FROM SESSION.reorgprd1 ;
OPEN clientcurr ;
END ;
END P1
ASKER
ocgstyles,
Thanks for your comments. Yes indeed, I was running the script as a file, first in the windows environment and tried same way in our linux environment - all of course failed.
I will act on your recommendation tomorrow at the office and report back to you/community what the result was.
Do you see any reason why the same script will not work in a RHEL linux environment?
Again thanks for your response! This community might very well be the best for what it teaches those of us that want to do things at the speed of light at a very very reasonable expense!
Enuda
Thanks for your comments. Yes indeed, I was running the script as a file, first in the windows environment and tried same way in our linux environment - all of course failed.
I will act on your recommendation tomorrow at the office and report back to you/community what the result was.
Do you see any reason why the same script will not work in a RHEL linux environment?
Again thanks for your response! This community might very well be the best for what it teaches those of us that want to do things at the speed of light at a very very reasonable expense!
Enuda
the reason the script did not work is that the declare statement is not a valid command line processor command
it does not matter on which platform you try to run this, as long as you don't put it in a stored procedure, it will fail
it does not matter on which platform you try to run this, as long as you don't put it in a stored procedure, it will fail
ASKER
moni sabaq,
Thanks for your comment. To further my search for solution, how would you resolve this problem? I am looking for solution given the basic thrust of this thread viz:
Script to call REORGCHK to select Tablespaces and pass these to REORG, all in one pass.
The solution that I presented (not yet re-tested as a stoproc) appears promising but you don't agree. Why? And how would you do it?
As I said, I will try the STORPROC route suggested by ocgstyles, but if you have a solution, please lets know.
Thanks
Thanks for your comment. To further my search for solution, how would you resolve this problem? I am looking for solution given the basic thrust of this thread viz:
Script to call REORGCHK to select Tablespaces and pass these to REORG, all in one pass.
The solution that I presented (not yet re-tested as a stoproc) appears promising but you don't agree. Why? And how would you do it?
As I said, I will try the STORPROC route suggested by ocgstyles, but if you have a solution, please lets know.
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
How are you executing this? Are you putting it into a stored procedure like the original example does? Since you are getting an error on every line, I'm lead to believe you are taking the EXACT code snippet you posted, putting into a file and running it like:
db2 -tvf "the_file.sql"
If so, that would not be correct. You need to put that into a stored procedure. If you copy and paste the following code into a file named "reorg.sql", then compile the stored procedure with:
db2 -td@ -f "reorg.sql"
Hope that helps.
Keith
Open in new window