Solved

REORGCHK-REORG Script not working. Can someone tell me what I am doing wrong?

Posted on 2007-11-19
5
1,465 Views
Last Modified: 2013-12-06
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
Comment
Question by:Enuda
  • 2
  • 2
5 Comments
 
LVL 5

Expert Comment

by:ocgstyles
ID: 20317151
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
 

Author Comment

by:Enuda
ID: 20317715
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 20318077
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
 

Author Comment

by:Enuda
ID: 20319147
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
 
LVL 5

Accepted Solution

by:
ocgstyles earned 250 total points
ID: 20319552
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

This article will explain how to establish a SSH connection to Ubuntu through the firewall and using a different port other then 22. I have set up a Ubuntu virtual machine in Virtualbox and I am running a Windows 7 workstation. From the Ubuntu vi…
You ever wonder how to backup Linux system files just like Windows System Restore?  Well you can use Timeshift in Linux to perform those similar action.  This tutorial will show you how to backup your system files and keep regular intervals. Note…
Learn the basics of modules and packages in Python. Every Python file is a module, ending in the suffix: .py: Modules are a collection of functions and variables.: Packages are a collection of modules.: Module functions and variables are accessed us…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now