Link to home
Start Free TrialLog in
Avatar of Enuda
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
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

Open in new window

Avatar of ocgstyles
ocgstyles

Hi Enuda,

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
CREATE PROCEDURE DB2ADMIN.myreorgts( )
DYNAMIC RESULT SETS 1
LANGUAGE SQL
MODIFIES SQL DATA
 
 
------------------------------------------------------------------------
-- SQL Stored Procedure
-- Reorg all recommended by reorgchk_tb_stats
-- Maintenace
-- Date Name Description
-- ---------- ------------- -------------------------------------------
-- 2005-11-18 Norm Wong Initial
------------------------------------------------------------------------
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;
DECLARE OVERFLOW INTEGER;
DECLARE NPAGES INTEGER;
DECLARE FPAGES INTEGER;
DECLARE ACTIVE_BLOCKS INTEGER;
DECLARE TSIZE INTEGER;
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
SET at_end = 1;
 
DECLARE GLOBAL TEMPORARY TABLE reorgtarg
(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 Tablespaces --
----------------------------------------------------------------------------
-- Call REORGCHK_TB_STATS for reorg recommendations
CALL REORGCHK_TB_STATS('T','ALL');
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
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.reorgtarg;
OPEN clientcurr;
END;
 
END P1@

Open in new window

Avatar of Enuda

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
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
Avatar of Enuda

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
ASKER CERTIFIED SOLUTION
Avatar of ocgstyles
ocgstyles

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial