Rebind Job

Raey

Rebind Job
Hello Listers,

I am looking for a JCL job to rebind all of the DB2 packages found in a
specific DB2 subsystem. Does any one have a sample job to do this?

Thanks,

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

Michael Ebert

Re: Rebind Job
(in response to Raey)
Do you mean something like this?

//BATCHDSN EXEC PGM=IKJEFT1B,PARM='DSN S(DB2T)'
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSTSIN DD *
REBIND PACKAGE(*.*.(*))

However, check the BINDTIME in SYSIBM.SYSPACKAGE after you run this. The
last time I did, I discovered there were lots of packages that were not
rebound without any error message or warning given. You can rebind
packages from DB2I option 5 as well, but I've never used that.

Dr. Michael Ebert
DB2 & Oracle Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany








Mark Kassa <[login to unmask email]>
Please respond to DB2 Database Discussion list at IDUG
<[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
15-09-06 06:30

To
[login to unmask email]
cc



Subject
[DB2-L] Rebind Job






Hello Listers,

I am looking for a JCL job to rebind all of the DB2 packages found in a
specific DB2 subsystem. Does any one have a sample job to do this?

Thanks,

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

Walter Jani&#223;en

Re: Rebind Job
(in response to Michael Ebert)
Hi

I use QMF to generate the BIND-statements.

To give you an example (here plan bind):

SELECT
'BIND PLAN('!!STRIP(NAME)!!')'!!
' OWNER(E$PPSB) QUALIFIER(DB2) '!!
' PKLIST(DB2.*) +?'!!
' ACTION(ADD) '!!
' VALIDATE(BIND) ISOLATION(CS) CACHESIZE(0) +?'!!
' ACQUIRE(USE) RELEASE(DEALLOCATE) '!!
' CURRENTDATA(NO)'
FROM SYSIBM.SYSPLAN

When the report is shown, I change the column width to 68 and the edit-mode
to CD?. Then I dump the report into a dataset, which is input for a batch
job using IEFTJ01B.

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

Roland Schiradin

Re: Rebind Job
(in response to Walter Janißen)
Hi Michael,

usually such a job will ends with RC8 becaue of bind errors. However check the last lines because the REBIND statement can be ended
with a strange msg like maximum number of lines or so. If memory serve me right.

Roland


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Michael Ebert
Sent: Friday, September 15, 2006 9:24 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Rebind Job



Do you mean something like this?

//BATCHDSN EXEC PGM=IKJEFT1B,PARM='DSN S(DB2T)'
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSTSIN DD *
REBIND PACKAGE(*.*.(*))

However, check the BINDTIME in SYSIBM.SYSPACKAGE after you run this. The last time I did, I discovered there were lots of packages that were not rebound without any error message or warning given. You can rebind packages from DB2I option 5 as well, but I've never used that.



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

Mark McCormack

Rebind Job
(in response to Roland Schiradin)
< Hello Listers,

< I am looking for a JCL job to rebind all of the DB2 packages found in a
< specific DB2 subsystem. Does any one have a sample job to do this?

Mark,

This reply comes several days after your post. I have been out of the
office for a week. I hope it will still be of interest.

This job should do what you want. I have run this kind of thing to rebind
after conversion to DB2v8. But I have never run it without extra
predicates in the WHERE clause of the SQL stmt. In particular, I select
based on OWNER or COLLID. I have also selected on VALID and/or OPERATIVE.
You might also consider sysibm.syspackage column RELBOUND. 'L' = bound
under DB2v8; 'K' = bound under DB2v7; blank = bound under DB2v6 or
earlier.

Mark

//* ******************************************************************
//* USE SQL QUERY VIA PGM=DSNTIAUL TO GENERATE
//* DB2 REBIND COMMANDS
//* ------------------------------------------------------------------
//GENREBIN EXEC PGM=IKJEFT01,DYNAMNBR=20,
// COND=(4,LT),REGION=4M
//STEPLIB DD - - - -
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSREC00 DD DSN=- - -,DISP=(NEW,CATLG,CATLG),
// UNIT=SYSDA,SPACE=(TRK,(5,10),RLSE)
//SYSTSIN DD *
DSN SYSTEM(- - -)
RUN PROG(DSNTIAUL) PLAN(DSNTIAUL) PARM('SQL')
/*
//SYSIN DD *
SELECT CAST(
'REBIND PACKAGE (' ||
STRIP(P.COLLID) ||
'.' ||
STRIP(P.NAME) ||
'.(' ||
STRIP(P.VERSION) ||
')' AS CHAR(80))
FROM SYSIBM.SYSPACKAGE P
WHERE P.TYPE <> 'T' - - - > excludes trigger packages
other where predicates as desired
ORDER BY 1 WITH UR;
/*
//SYSPUNCH DD DUMMY
//*
//* ********************************************************
//* RUN THE REBIND COMMANDS CREATED
//* IN PREVIOUS STEP
//*---------------------------------------------------------
//REBIND EXEC PGM=IKJEFT01,DYNAMNBR=20,
// COND=(4,LT),REGION=4M
//STEPLIB DD - - - -
/SYSTSIN DD *
DSN SYSTEM(- - -)
/*
// DD DISP=OLD,DSN=sysrec00 from previous step
//SYSTSPRT DD SYSOUT=*

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

Rambabu Vanama

Rebind
(in response to Mark McCormack)
Is it necessary to do the REBIND after performing the REORG or RUNSTATS? I
have always been thinking that we should perform Rebind after the
Reorg/Runstats to see the effect of Reorg/Runstats. But I recently heard
that it is not necessary to do the Rebind and could have negative impact if
we do.

Appreciate all your responses.

-Rambabu Vanama

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

James Campbell

Re: Rebind
(in response to Rambabu Vanama)
Necessary - in that will you get incorrect results if you do not rebind - no. (Bugs excepted.)

Necessary - in that will you get improved performance - depends. There are some
situations where people have carefully crafted binds to get optimal response and a rebind
would undo the care; however it is also possible to get better performance after a rebind.

Generally the ideal would be to re-bind into a dummy collection and compare the
PLAN_TABLE stuff for the production and dummy entries. If they are the same, you don't
need to rebind the production; if they are different, determine which is better and act
accordingly. If this is after a RUNSTATS, you might find undesirable access paths - which
might be the signal to do a reorg.

As the production stuff was, presumably, rebound after the previous reorg there should be
little, if any change.

James Campbell

On 30 Nov 2006 at 9:43, Rambabu Vanama wrote:

> Is it necessary to do the REBIND after performing the REORG or RUNSTATS? I
> have always been thinking that we should perform Rebind after the
> Reorg/Runstats to see the effect of Reorg/Runstats. But I recently heard
> that it is not necessary to do the Rebind and could have negative impact if
> we do.
>
> Appreciate all your responses.
>
> -Rambabu Vanama
>

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

Larry Jardine

Re: Rebind
(in response to James Campbell)
REBIND (o
Sometimes working backwards is easier to understand.

REBIND (only) when you want access paths to change. Otherwise, why
bother?
Before you REBIND, make sure statistics are current: do RUNSTATS.
Before you do RUNSTATS, make sure you will get "good" statistics: REORG.

Put another way, for a heavy-loaded transaction environment with mostly
static SQL:
- REORG frequently.
- Do RUNSTATS only after reorgs (or inline with the REORG).
- REBIND rarely (only when you want different access paths).

For dynamic SQL and environments where the data is very volatile:
- Do RUNSTATS frequently.
- REORG as often as you can.
- REBIND doesn't apply for dynamic SQL.


Larry Jardine
Production DBA

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of James Campbell
Sent: Thursday, November 30, 2006 7:31 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Rebind

Necessary - in that will you get incorrect results if you do not rebind
- no. (Bugs excepted.)

Necessary - in that will you get improved performance - depends. There
are some situations where people have carefully crafted binds to get
optimal response and a rebind would undo the care; however it is also
possible to get better performance after a rebind.

Generally the ideal would be to re-bind into a dummy collection and
compare the PLAN_TABLE stuff for the production and dummy entries. If
they are the same, you don't need to rebind the production; if they are
different, determine which is better and act accordingly. If this is
after a RUNSTATS, you might find undesirable access paths - which might
be the signal to do a reorg.

As the production stuff was, presumably, rebound after the previous
reorg there should be little, if any change.

James Campbell

On 30 Nov 2006 at 9:43, Rambabu Vanama wrote:

> Is it necessary to do the REBIND after performing the REORG or
> RUNSTATS? I have always been thinking that we should perform Rebind
> after the Reorg/Runstats to see the effect of Reorg/Runstats. But I
> recently heard that it is not necessary to do the Rebind and could
> have negative impact if we do.
>
> Appreciate all your responses.
>
> -Rambabu Vanama
>

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

-----------------------------------------
This e-mail may contain confidential or privileged information. If
you think you have received this e-mail in error, please advise the
sender by reply e-mail and then delete this e-mail immediately.
Thank you. Aetna


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