[DB2-L] DB2 V7 z/os - Transferring Runstats info

Philip Sevetson

[DB2-L] DB2 V7 z/os - Transferring Runstats info
Could you use a GUI client like WinSQL Lite?  You'd have to have your files on the client, and have an ODBC connection.

--Phil Sevetson, NYCAPS DBA Support
Financial Information Services Agency of The City of New York
450 West 33rd Street, 4th Floor
New York, NY 10001
phone: (212) 857-1688
mailto: [login to unmask email]
________________________________________
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]


=========
Confidentiality Notice: This e-mail communication, and any attachments, contains confidential and privileged information for the exclusive use of the recipient(s) named above. If you are not an intended recipient, or the employee or agent responsible to deliver it to an intended recipient, you are hereby notified that you have received this communication in error and that any review, disclosure, dissemination, distribution or copying of it or its contents is prohibited. If you have received this communication in error, please notify me immediately by replying to this message and delete this communication from your computer. Thank you.

Any opinions, expressed or implied, presented are solely those of the author and do not necessarily represent the opinions of the agency or the City.
=========



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 Philip Sevetson)
Jeff - if I understood your questions correctly - I believe this will
solve your issue:



1) Execute your SELECT statement using CA Batch Processor as a
batch job and include the //PTISELDD DD as the output dataset.

2) When submitting the BP job - specify WRAP/TRUNCATE=Y. This will
wrap each line and both SPUFI, DSNTEP2 and CA Batch Processor will be
able to execute the result set created.



Hope this solves your issue ?



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 Wolters, Jeff B.
Sent: Tuesday, December 04, 2007 10: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.

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

Lockwood Lyon

Re: DB2 V7 z/os - Transferring Runstats info
(in response to Steen Rasmussen)
There is a great article in the Summer 2007 IDUG Solutions Journal
titled, "Tips for Copying Catalog Statistics from Production to Test
Subsystems" that discusses alternatives.

Lock Lyon
Fifth Third Bancorp


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

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



This e-mail transmission contains information that is confidential and may be privileged. It is intended only for the addressee(s) named above. If you receive this e-mail in error, please do not read, copy or disseminate it in any manner. If you are not the intended recipient, any disclosure, copying, distribution or use of the contents of this information is prohibited. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please erase it from your computer system. Your assistance in correcting this error is appreciated.

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

Jeff Wolters

Re: DB2 V7 z/os - Transferring Runstats info
(in response to Lockwood Lyon)
Yes, the problem is the generated SQL is greater than 80 bytes. (there
are over 3600 lines, so its not easy to edit and wrap around the text).



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

================

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]
On Behalf Of Rasmussen, Steen
Sent: Tuesday, December 04, 2007 11:19 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 V7 z/os - Transferring Runstats info



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


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

Jeff Wolters

Re: DB2 V7 z/os - Transferring Runstats info
(in response to Jeff Wolters)
Thanks Steen, I'll give it a try. Thanks Lock. I'll look the "tip" up.



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

================

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]
On Behalf Of Rasmussen, Steen
Sent: Tuesday, December 04, 2007 11:24 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 V7 z/os - Transferring Runstats info



Jeff - if I understood your questions correctly - I believe this
will solve your issue:



1) Execute your SELECT statement using CA Batch Processor
as a batch job and include the //PTISELDD DD as the output dataset.

2) When submitting the BP job - specify WRAP/TRUNCATE=Y.
This will wrap each line and both SPUFI, DSNTEP2 and CA Batch Processor
will be able to execute the result set created.



Hope this solves your issue ?



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 Wolters, Jeff B.
Sent: Tuesday, December 04, 2007 10: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.

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


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

Richard Fazio

Re: DB2 V7 z/os - Transferring Runstats info
(in response to Jeff Wolters)
I wrote a REXX (SQLPROC) that processes any sequential dataset with SQL
in it. It re-parses the SQL in the dataset, so in theory, it could
handle any LRECL. This processes like DSNTIAD (NO SELECTS).



I can email you the code if you like.

faz





________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Wolters, Jeff B.
Sent: Tuesday, December 04, 2007 10: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.

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

David Seibert

Re: DB2 V7 z/os - Transferring Runstats info
(in response to Richard Fazio)
Ahhhhh. Tough one.

Can you edit the 3600 lines?
I have an edit macro in Rexx that automates text-split at a column
location.

If you can edit the lines, you would issue my macro as ===> XTSPLIT 80.

No guarantees --- You might run into memory or a bug in my code or ISPF
issues.
If you're interested, for the proper exchange of fees, I could send you
the macro.
(Just kidding about the fees) It's free and worth every penny of it.

Dave




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.


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


Yes, the problem is the generated SQL is greater than 80 bytes. (there
are over 3600 lines, so its not easy to edit and wrap around the text).



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

================

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]
On Behalf Of Rasmussen, Steen
Sent: Tuesday, December 04, 2007 11:19 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 V7 z/os - Transferring Runstats info



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

Shery hepp

Re: DB2 V7 z/os - Transferring Runstats info
(in response to David Seibert)
Hi Jeff- have you considered using service sql from visual explain? You
plug your query in and run the generate files. You can then edit your
stats file from the prod names to test cut and paste into db2 commmand
center and run. It will update all of the stats from the catalog for
that query. I have used it on numerous occasions.



Hope this helps.



Regards, Shery



________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Wolters, Jeff B.
Sent: Tuesday, December 04, 2007 9: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



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

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

Jeff Wolters

Re: DB2 V7 z/os - Transferring Runstats info
(in response to Shery hepp)
Steen,
I received this when using your solution, below:
BPA0093I: THE PTISELDD DATASET MUST BE ALLOCATED WITH A MINIMUM 80

BYTE LRECL FOR FIXED RECORDS OR 84 BYTE LRECL FOR VARIABLE

RECORDS. PROCESSING CONTINUES WITHOUT WRITING THE RESULTS TO

PTISELDD.




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

================

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]
On Behalf Of Rasmussen, Steen
Sent: Tuesday, December 04, 2007 11:24 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 V7 z/os - Transferring Runstats info



Jeff - if I understood your questions correctly - I believe this
will solve your issue:



1) Execute your SELECT statement using CA Batch Processor
as a batch job and include the //PTISELDD DD as the output dataset.

2) When submitting the BP job - specify WRAP/TRUNCATE=Y.
This will wrap each line and both SPUFI, DSNTEP2 and CA Batch Processor
will be able to execute the result set created.



Hope this solves your issue ?



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 Wolters, Jeff B.
Sent: Tuesday, December 04, 2007 10: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.

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


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

Phil Grainger

Re: DB2 V7 z/os - Transferring Runstats info
(in response to Jeff Wolters)
I think that's what Steen meant - create an 80 byte FBA file which will contain the SQL, but also specify WRAP/TRUNCATE=Y so that the subsequent run of the GENERATED SQL knows that the SQL is on multiple input records not on a single one

Phil Grainger
CA

________________________________

From: DB2 Data Base Discussion List on behalf of Wolters, Jeff B.
Sent: Tue 04/12/2007 18:03
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 V7 z/os - Transferring Runstats info


Steen,
I received this when using your solution, below:
BPA0093I: THE PTISELDD DATASET MUST BE ALLOCATED WITH A MINIMUM 80

BYTE LRECL FOR FIXED RECORDS OR 84 BYTE LRECL FOR VARIABLE

RECORDS. PROCESSING CONTINUES WITHOUT WRITING THE RESULTS TO

PTISELDD.




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

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Rasmussen, Steen
Sent: Tuesday, December 04, 2007 11:24 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 V7 z/os - Transferring Runstats info



Jeff - if I understood your questions correctly - I believe this will solve your issue:



1) Execute your SELECT statement using CA Batch Processor as a batch job and include the //PTISELDD DD as the output dataset.

2) When submitting the BP job - specify WRAP/TRUNCATE=Y. This will wrap each line and both SPUFI, DSNTEP2 and CA Batch Processor will be able to execute the result set created.



Hope this solves your issue ?



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 Wolters, Jeff B.
Sent: Tuesday, December 04, 2007 10: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.

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

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

Steve Whittaker

Re: DB2 V7 z/os - Transferring Runstats info
(in response to Phil Grainger)
Shery:
I like your suggestion on using the Visual Explain product. I had
notice that feature in there but had never really drilled down into it.
All sorts of nice ideas coming from this thread.


--Steve....

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]
On Behalf Of Hepp Shery C
Sent: Tuesday, December 04, 2007 12:08 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 V7 z/os - Transferring Runstats info



Hi Jeff- have you considered using service sql from visual
explain? You plug your query in and run the generate files. You can then
edit your stats file from the prod names to test cut and paste into db2
commmand center and run. It will update all of the stats from the
catalog for that query. I have used it on numerous occasions.



Hope this helps.



Regards, Shery




________________________________


From: DB2 Data Base Discussion List [mailto:[login to unmask email]
On Behalf Of Wolters, Jeff B.
Sent: Tuesday, December 04, 2007 9: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



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 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

Paul Fegan

Re: DB2 V7 z/os - Transferring Runstats info
(in response to Steve Whittaker)
Jeff,

I have a REXX exec that generates a list of tables in the target system
and then goes and grabs the stats from source system and stuffs it into
the target systems catalog. It's a bit convoluted and take about 30
minutes to drag back all the stats for our system (about 800 tables). If
you want to have a look at it I can email you a copy.


Paul Fegan
DB2 Database Administrator
_____________________________________________________________
INFORMATION MANAGEMENT DIVISION | Queensland Transport
Creating business confidence

477 Boundary Street, Spring Hill QLD 4000
P: 07 3834 5022 F: 07 3834 2911
M: 0433 039 360
E: [login to unmask email]



"Wolters, Jeff B." <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
05/12/2007 02:03 AM
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc

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


***********************************************************************
WARNING: This e-mail (including any attachments) may contain legally
privileged, confidential or private information and may be protected by
copyright. You may only use it if you are the person(s) it was intended
to be sent to and if you use it in an authorised way. No one is
allowed to use, review, alter, transmit, disclose, distribute, print
or copy this e-mail without appropriate authority.

If this e-mail was not intended for you and was sent to you by mistake,
please telephone or e-mail me immediately, destroy any hardcopies of
this e-mail and delete it and any copies of it from your computer
system. Any right which the sender may have under copyright law, and
any legal privilege and confidentiality attached to this e-mail is not
waived or destroyed by that mistake.

It is your responsibility to ensure that this e-mail does not contain
and is not affected by computer viruses, defects or interference by
third parties or replication problems (including incompatibility with
your computer system).

Opinions contained in this e-mail do not necessarily reflect the
opinions of the Queensland Department of Main Roads, Queensland
Transport or Maritime Safety Queensland, or endorsed organisations
utilising the same infrastructure.
***********************************************************************


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

Alexandros Papadopoulos

Re: DB2 V7 z/os - Transferring Runstats info
(in response to Paul Fegan)
Jeff,
Maybe you were actually looking for something like that (based on the queries you provided, and not tested)
SELECT LEFT(COL1,POSSTR(COL1,'$')-1) CONCAT
' ',DB,TS,1
FROM (SELECT DISTINCT 'UPDATE SYSIBM.SYSTABLESPACE SET NACTIVEF='
CONCAT STRIP(CHAR(NACTIVEF))
CONCAT ',NACTIVE=' CONCAT STRIP(CHAR(NACTIVE))
CONCAT ' $WHERE NAME=''' CONCAT STRIP(TS.NAME,T)
CONCAT ''' AND DBNAME=''' CONCAT STRIP(TS.DBNAME,T) CONCAT''';' AS COL1,
TS.DBID AS DB,TS.PSID AS TS
FROM SYSIBM.SYSTABLESPACE TS, SYSIBM.SYSTABLES TBL
WHERE TS.NAME= TSNAME AND TBL.CREATOR IN ('PROD1')
AND (NACTIVEF >=0 OR NACTIVE >=0)) A
UNION ALL
SELECT SUBSTR(COL1,POSSTR(COL1,'$')+1) CONCAT
' ',DB,TS,2
FROM (SELECT DISTINCT 'UPDATE SYSIBM.SYSTABLESPACE SET NACTIVEF='
CONCAT STRIP(CHAR(NACTIVEF))
CONCAT ',NACTIVE=' CONCAT STRIP(CHAR(NACTIVE))
CONCAT ' $WHERE NAME=''' CONCAT STRIP(TS.NAME,T)
CONCAT ''' AND DBNAME='''
CONCAT STRIP(TS.DBNAME,T) CONCAT''';' AS COL1,
TS.DBID AS DB,TS.PSID AS TS
FROM SYSIBM.SYSTABLESPACE TS, SYSIBM.SYSTABLES TBL
WHERE TS.NAME= TSNAME AND TBL.CREATOR IN ('PROD1')
AND (NACTIVEF >=0 OR NACTIVE >=0)) A ORDER BY 2,3,4;
---
SELECT 'UPDATE SYSIBM.SYSTABLES SET CARDF=' CONCAT STRIP(CHAR(CARDF))
CONCAT ' ',
DBID,OBID,1 FROM SYSIBM.SYSTABLES WHERE CREATOR IN ('PROD1')
AND CARDF >= 0 UNION ALL
SELECT ',NPAGES=' CONCAT STRIP(CHAR(NPAGES))
CONCAT ',PCTROWCOMP=' CONCAT STRIP(CHAR(PCTROWCOMP))
CONCAT ' ',
DBID,OBID,2 FROM SYSIBM.SYSTABLES WHERE CREATOR IN ('PROD1')
AND CARDF >= 0 UNION ALL
SELECT ' WHERE NAME=''' CONCAT STRIP(NAME,T)
CONCAT ''' AND CREATOR =''' CONCAT STRIP(CREATOR,T)
CONCAT '''; ',
DBID,OBID,3 FROM SYSIBM.SYSTABLES WHERE CREATOR IN ('PROD1')
AND CARDF >= 0 ORDER BY 2,3,4;
---
SELECT 'UPDATE SYSIBM.SYSINDEXES SET FIRSTKEYCARDF='
CONCAT STRIP(CHAR(FIRSTKEYCARDF))
CONCAT ' ',
DBID,ISOBID,1 FROM SYSIBM.SYSINDEXES
WHERE TBCREATOR IN ('PROD1') AND FULLKEYCARDF >= 0
UNION ALL
SELECT ',FULLKEYCARDF=' CONCAT STRIP(CHAR(FULLKEYCARDF))
CONCAT ',NLEAF=' CONCAT STRIP(CHAR(NLEAF))
CONCAT ',NLEVELS=' CONCAT STRIP(CHAR(NLEVELS))
CONCAT ',CLUSTERRATIO=' CONCAT STRIP(CHAR(CLUSTERRATIO))
CONCAT ' ',
DBID,ISOBID,2 FROM SYSIBM.SYSINDEXES
WHERE TBCREATOR IN ('PROD1') AND FULLKEYCARDF >= 0
UNION ALL
SELECT ',CLUSTERRATIOF=' CONCAT STRIP(CHAR(CLUSTERRATIOF))
CONCAT ' ',
DBID,ISOBID,3 FROM SYSIBM.SYSINDEXES
WHERE TBCREATOR IN ('PROD1') AND FULLKEYCARDF >= 0
UNION ALL
SELECT ' WHERE NAME=''' CONCAT STRIP(NAME,T)
CONCAT ''' AND CREATOR =''' CONCAT STRIP(CREATOR,T) CONCAT''';'
CONCAT ' ',
DBID,ISOBID,4 FROM SYSIBM.SYSINDEXES
WHERE TBCREATOR IN ('PROD1') AND FULLKEYCARDF >= 0 ORDER BY 2,3,4;
---
SELECT 'UPDATE SYSIBM.SYSCOLUMNS SET COLCARDF='
CONCAT STRIP(CHAR(COLCARDF))
CONCAT ' ',
TBCREATOR,TBNAME,COLNO,1
FROM SYSIBM.SYSCOLUMNS WHERE TBCREATOR IN ('PROD1') AND COLCARDF >= 0
UNION ALL
SELECT ',HIGH2KEY= X''' CONCAT HEX(HIGH2KEY)
CONCAT ''',LOW2KEY= X''' CONCAT HEX(LOW2KEY) CONCAT ''''
CONCAT ' ',
TBCREATOR,TBNAME,COLNO,2
FROM SYSIBM.SYSCOLUMNS WHERE TBCREATOR IN ('PROD1') AND COLCARDF >= 0
UNION ALL
SELECT ' WHERE TBNAME=''' CONCAT STRIP(TBNAME,T) CONCAT ''' AND COLNO='
CONCAT STRIP(CHAR(COLNO)) CONCAT ' AND TBCREATOR ='''
CONCAT STRIP(TBCREATOR,T) CONCAT''';'
CONCAT ' ',
TBCREATOR,TBNAME,COLNO,3 FROM SYSIBM.SYSCOLUMNS
WHERE TBCREATOR IN ('PROD1') AND COLCARDF >= 0 ORDER BY 2,3,4,5;
Hth,
Regards
Alekos


***************************************************************************************
Äéåõêñßíéóç çëåêôñïíéêïý ôá÷õäñïìåßïõ
Ïé ðëçñïöïñßåò ðïõ óõìðåñéëáìâÜíïíôáé óå áõôü ôï ìÞíõìá åßíáé åìðéóôåõôéêÝò êáé ç ÷ñÞóç ôïõò åðéôñÝðåôáé ìüíïí áðü ôïí áíáöåñüìåíï ðáñáëÞðôç. ÅÜí Ý÷åôå ëÜâåé ôï ðáñüí ìÞíõìá áðü ëÜèïò êáé äåí åßóôå ï ðñïïñéæüìåíïò ðáñáëÞðôçò, óáò åíçìåñþíïõìå üôé áðïêÜëõøç, áíáðáñáãùãÞ, äéáíïìÞ Þ ïðïéáóäÞðïôå Üëëçò ìïñöÞò ÷ñÞóç ôùí ðåñéå÷ïìÝíùí ôïõ ðáñüíôïò ìçíýìáôïò áðáãïñåýåôáé. Åðßóçò ðáñáêáëåßóèå íá áðïóôåßëåôå ôï áñ÷éêü ìÞíõìá óôç äéåýèõíóç [login to unmask email], êáèþò êáé óôç óõíÝ÷åéá íá äéáãñÜøåôå ôï ìÞíõìá áðü ôï óýóôçìÜ óáò.
Ïé åðéêïéíùíßåò ìÝóù ôïõ Äéáäéêôýïõ äåí åßíáé áóöáëåßò êáé ãéá ôïí ëüãï áõôü ï ¼ìéëïò Åôáéñéþí ôçò ÅèíéêÞò ÔñÜðåæáò ôçò ÅëëÜäïò äåí áðïäÝ÷åôáé íïìéêÞ åõèýíç ãéá ôá ðåñéå÷üìåíá ôïõ ðáñüíôïò ìçíýìáôïò êáé ãéá ïðïéáäÞðïôå æçìéÜ ðñïêëçèåß áðü éïýò ðïõ åßíáé äõíáôüí íá åéóáãÜãåé. Ïé áðüøåéò ðïõ äéáôõðþíïíôáé áíÞêïõí áðïêëåéóôéêÜ óôïí áðïóôïëÝá ôïõ ìçíýìáôïò êáé äåí áíôéðñïóùðåýïõí áðáñáßôçôá ôéò áðüøåéò ôïõ Ïìßëïõ Åôáéñéþí ôçò ÅèíéêÞò ÔñÜðåæáò ôçò ÅëëÜäïò.
Óáò Åõ÷áñéóôïýìå,
¼ìéëïò Åôáéñéþí ÅèíéêÞò ÔñÜðåæáò ôçò ÅëëÜäïò

Email Disclaimer
The information in this email is confidential and is intended solely for the addressee(s). If you have received this transmission in error, and you are not an intended recipient, be aware that any disclosure, copying, distribution or use of this transmission or its contents is prohibited. Furthermore, you are kindly requested to send us back the original message at the address [login to unmask email], and delete the message from your system immediately.
Internet communications are not secure and therefore the National Bank of Greece Group does not accept legal responsibility for the contents of this message and for any damage whatsoever that is caused by viruses being passed. Any views or opinions presented are solely those of the author and do not necessarily represent those of National Bank of Greece Group.
Thank You,
National Bank of Greece Group
***************************************************************************************

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

Frederic Lavoie

Re: DB2 V7 z/os - Transferring Runstats info
(in response to Alexandros Papadopoulos)
Yes please, this is something we need often



Thank you in advance.

Fred Lavoie
DB Common Group
*613-954-9286

________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Fazio, Richard
Sent: December 4, 2007 11:39 AM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 V7 z/os - Transferring Runstats info



I wrote a REXX (SQLPROC) that processes any sequential dataset with SQL
in it. It re-parses the SQL in the dataset, so in theory, it could
handle any LRECL. This processes like DSNTIAD (NO SELECTS).



I can email you the code if you like.

faz





________________________________

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Wolters, Jeff B.
Sent: Tuesday, December 04, 2007 10: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.

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

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