DB2 V7 z/os - Transferring Runstats info

Jeff Wolters

DB2 V7 z/os - Transferring Runstats info
Does anyone know how or a utility that can be used, to run SQL input,
that is larger than 80 bytes?

I'm trying to move statistics from one DB2 subsystem to another (prod to
test). I have the four SQL queries from the Administration Guide Vol 2
(see below). I've tried using SPUFI, DSNTEP2 & DSNTIAD, CA-Unicenter
DB2 products(Platinum),
But all have limitations on the length of the input. (And I didn't see
any parameters to override the length).
The SQL to build the SQL, to do the updates, creates update statements
that are
Almost 180 bytes long.

SELECT DISTINCT 'UPDATE SYSIBM.SYSTABLESPACE SET NACTIVEF='
CONCAT STRIP(CHAR(NACTIVEF))
CONCAT ',NACTIVE=' CONCAT STRIP(CHAR(NACTIVE))
CONCAT ' WHERE NAME=''' CONCAT TS.NAME
CONCAT ''' AND DBNAME=''' CONCAT TS.DBNAME CONCAT'''*'
FROM SYSIBM.SYSTABLESPACE TS, SYSIBM.SYSTABLES TBL
WHERE TS.NAME= TSNAME
AND TBL.CREATOR IN ('PROD1')
AND (NACTIVEF >=0 OR NACTIVE >=0);

SELECT 'UPDATE SYSIBM.SYSTABLES SET CARDF='
CONCAT STRIP(CHAR(CARDF))
CONCAT ',NPAGES=' CONCAT STRIP(CHAR(NPAGES))
CONCAT ',PCTROWCOMP=' CONCAT STRIP(CHAR(PCTROWCOMP))
CONCAT ' WHERE NAME=''' CONCAT NAME
CONCAT ''' AND CREATOR =''' CONCAT CREATOR CONCAT'''*'
FROM SYSIBM.SYSTABLES
WHERE CREATOR IN ('PROD1')
AND CARDF >= 0;

SELECT 'UPDATE SYSIBM.SYSINDEXES SET FIRSTKEYCARDF='
CONCAT STRIP(CHAR(FIRSTKEYCARDF))
CONCAT ',FULLKEYCARDF=' CONCAT STRIP(CHAR(FULLKEYCARDF))
CONCAT ',NLEAF=' CONCAT STRIP(CHAR(NLEAF))
CONCAT ',NLEVELS=' CONCAT STRIP(CHAR(NLEVELS))
CONCAT ',CLUSTERRATIO=' CONCAT STRIP(CHAR(CLUSTERRATIO))
CONCAT ',CLUSTERRATIOF=' CONCAT STRIP(CHAR(CLUSTERRATIOF))
CONCAT ' WHERE NAME=''' CONCAT NAME
CONCAT ''' AND CREATOR =''' CONCAT CREATOR CONCAT'''*'
FROM SYSIBM.SYSINDEXES
WHERE TBCREATOR IN ('PROD1')
AND FULLKEYCARDF >= 0;

SELECT 'UPDATE SYSIBM.SYSCOLUMNS SET COLCARDF='
CONCAT STRIP(CHAR(COLCARDF))
CONCAT ',HIGH2KEY= X''' CONCAT HEX(HIGH2KEY)
CONCAT ''',LOW2KEY= X''' CONCAT HEX(LOW2KEY)
CONCAT ''' WHERE TBNAME=''' CONCAT TBNAME CONCAT ''' AND COLNO='
CONCAT STRIP(CHAR(COLNO))
CONCAT ' AND TBCREATOR =''' CONCAT TBCREATOR CONCAT'''*'
FROM SYSIBM.SYSCOLUMNS
WHERE TBCREATOR IN ('PROD1')
AND COLCARDF >= 0;

Jeff Wolters
DataBase Services
Information Technology
Progress Energy
Internal: 8-230-5325
External: 727-820-5325
Email: [login to unmask email]


IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

David Seibert

Re: DB2 V7 z/os - Transferring Runstats info
(in response to Jeff Wolters)
Hi Jeff.

In SPUFI and DSNTEP2, you can use continuation:
You don't need any special continuation character. Just put the name in
quotes and run it all the way to col. 72 and resume in col. 1.
Subsequent testing shows you don't even need quotes.
CREATE PROCEDURE

"SPWITHREALLYLONGNAME
The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it.

_______________________________"

(IN CHAR(6))

LANGUAGE COBOL

NOT DETERMINISTIC

MODIFIES SQL DATA

EXTERNAL NAME SPLONGNM

COLLID D845DJS

STAY RESIDENT NO

WLM ENVIRONMENT DB2WLMD845

PROGRAM TYPE MAIN

PARAMETER STYLE DB2SQL

COMMIT ON RETURN NO;

Dave



________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Wolters, Jeff B.
Sent: Tuesday, December 04, 2007 11:04 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 V7 z/os - Transferring Runstats info



Does anyone know how or a utility that can be used, to run SQL input,
that is larger than 80 bytes?

I'm trying to move statistics from one DB2 subsystem to another (prod to
test). I have the four SQL queries from the Administration Guide Vol 2
(see below). I've tried using SPUFI, DSNTEP2 & DSNTIAD, CA-Unicenter
DB2 products(Platinum),

But all have limitations on the length of the input. (And I didn't see
any parameters to override the length).
The SQL to build the SQL, to do the updates, creates update statements
that are
Almost 180 bytes long.

SELECT DISTINCT 'UPDATE SYSIBM.SYSTABLESPACE SET NACTIVEF='
CONCAT STRIP(CHAR(NACTIVEF))
CONCAT ',NACTIVE=' CONCAT STRIP(CHAR(NACTIVE))
CONCAT ' WHERE NAME=''' CONCAT TS.NAME
CONCAT ''' AND DBNAME=''' CONCAT TS.DBNAME CONCAT'''*'
FROM SYSIBM.SYSTABLESPACE TS, SYSIBM.SYSTABLES TBL
WHERE TS.NAME= TSNAME
AND TBL.CREATOR IN ('PROD1')
AND (NACTIVEF >=0 OR NACTIVE >=0);

SELECT 'UPDATE SYSIBM.SYSTABLES SET CARDF='
CONCAT STRIP(CHAR(CARDF))
CONCAT ',NPAGES=' CONCAT STRIP(CHAR(NPAGES))
CONCAT ',PCTROWCOMP=' CONCAT STRIP(CHAR(PCTROWCOMP))
CONCAT ' WHERE NAME=''' CONCAT NAME
CONCAT ''' AND CREATOR =''' CONCAT CREATOR CONCAT'''*'
FROM SYSIBM.SYSTABLES
WHERE CREATOR IN ('PROD1')
AND CARDF >= 0;

SELECT 'UPDATE SYSIBM.SYSINDEXES SET FIRSTKEYCARDF='
CONCAT STRIP(CHAR(FIRSTKEYCARDF))
CONCAT ',FULLKEYCARDF=' CONCAT STRIP(CHAR(FULLKEYCARDF))
CONCAT ',NLEAF=' CONCAT STRIP(CHAR(NLEAF))
CONCAT ',NLEVELS=' CONCAT STRIP(CHAR(NLEVELS))
CONCAT ',CLUSTERRATIO=' CONCAT STRIP(CHAR(CLUSTERRATIO))
CONCAT ',CLUSTERRATIOF=' CONCAT STRIP(CHAR(CLUSTERRATIOF))
CONCAT ' WHERE NAME=''' CONCAT NAME
CONCAT ''' AND CREATOR =''' CONCAT CREATOR CONCAT'''*'
FROM SYSIBM.SYSINDEXES
WHERE TBCREATOR IN ('PROD1')
AND FULLKEYCARDF >= 0;

SELECT 'UPDATE SYSIBM.SYSCOLUMNS SET COLCARDF='
CONCAT STRIP(CHAR(COLCARDF))
CONCAT ',HIGH2KEY= X''' CONCAT HEX(HIGH2KEY)
CONCAT ''',LOW2KEY= X''' CONCAT HEX(LOW2KEY)
CONCAT ''' WHERE TBNAME=''' CONCAT TBNAME CONCAT ''' AND COLNO='
CONCAT STRIP(CHAR(COLNO))
CONCAT ' AND TBCREATOR =''' CONCAT TBCREATOR CONCAT'''*'
FROM SYSIBM.SYSCOLUMNS
WHERE TBCREATOR IN ('PROD1')
AND COLCARDF >= 0;

Jeff Wolters
DataBase Services
Information Technology
Progress Energy
Internal: 8-230-5325
External: 727-820-5325
Email: [login to unmask email]

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Steen Rasmussen

Re: DB2 V7 z/os - Transferring Runstats info
(in response to David Seibert)
Dave - I believe his INPUT dataset has a RECL greater than 80 byte which
is the problem - I'm currently looking into how ISQL or Batch Processor
can deal with this - is this a correct assumption Jeff ?



Steen Rasmussen
CA

Principal Technical Specialist DB2 tools

IBM Certified Database Associate -- DB2 9 Fundamentals

IBM Certified Database Administrator - DB2 9 DBA for z/OS

________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Seibert, Dave
Sent: Tuesday, December 04, 2007 10:12 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 V7 z/os - Transferring Runstats info



Hi Jeff.



In SPUFI and DSNTEP2, you can use continuation:

You don't need any special continuation character. Just put the name in
quotes and run it all the way to col. 72 and resume in col. 1.
Subsequent testing shows you don't even need quotes.

CREATE PROCEDURE
"SPWITHREALLYLONGNAME





IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion
listserv that are being implemented to improve reliability and the
overall user experience of DB2-L. These changes are coming on November
30th. Details at http://www.idug.org

------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm