[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1545
  • Last Modified:

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

0
Enuda
Asked:
Enuda
  • 2
  • 2
1 Solution
 
ocgstylesCommented:
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

0
 
EnudaAuthor Commented:
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
0
 
momi_sabagCommented:
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
0
 
EnudaAuthor Commented:
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
0
 
ocgstylesCommented:
Hi Eduda,

I see no reason why the my above recommendation won't work on RHEL.  It worked fine on Ubuntu.  As recommended by momi_sabag and me, you'll need to put the code into a stored procedure.  That is likely the reason for the initial failure.

Keith
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now