Posted: Sun Dec 16, 2007 2:32 pm Post subject: How can you check BIND to avoid a -805 on first run.
Hi
At our site we get frequent repeat problems caused by the DB2 BIND being wrong when a project implements an amended or new program.
Is there any check that develpers can do once the BIND has been done to predict if they are going to get a -805 (or any other bind error) beore the SQL runs in live?
My DBA area is saying that there isn't but I can't believe there isn't a way of doing it, either via EXPLAIN, Fileaid or something??
Any help appreciated as I'd like to produce a standard checklist for developers to avoid such errors in the future.
Joined: 26 Nov 2002 Posts: 12378 Topics: 75 Location: San Jose
Posted: Mon Dec 17, 2007 10:56 am Post subject:
packerm,
There is a rexx routine(not written by me) which compares the consistency token of the load module and the Consistency token for the package from sysibm.syspackage table. If you are interested let me know and I will post it
kolusu - yep very interested. either post it. Many Thanks.
Also been doing a bit more digging into this and came across the IBM Bind manager that has a "DBRM checker". From the description on the IBM site here ...
RETURN
/****************************************************************/
catinfo:
say "getting catalog info from prod / devl / test. " time()
call db2init right(mvs,1)
address sql
" select strip(collid),hex(contoken) into :collp,:contokp " ,
" from prod.sysibm.syspackage where name='"parm"' order by 2 desc"
" select strip(collid),hex(contoken) into :colld,:contokd " ,
" from devl.sysibm.syspackage where name='"parm"' order by 2 desc"
" select strip(collid),hex(contoken) into :collt,:contokt " ,
" from test.sysibm.syspackage where name='"parm"' order by 2 desc"
call db2end
say "finished getting catalog data from all environments" time()
RETURN
/****************************************************************/
CHKLIB:
PARSE UPPER ARG lib1 env
IF SYSDSN(lib1) <> OK then
say " MEMBER " lib1 " NOT FOUND "
ELSE call text ;
RETURN
/****************************************************************/
nextlib:
PARSE UPPER ARG env
DO I=1 TO LINE.0
CALL STMT
END
queue sql
RETURN
/****************************************************************/
STMT:
IF FIRST = 0 THEN
DO;
FIRST = 1;
PARSE VAR LINE.I 9 user +8,
17 name +8,
25 tokn +8
/* split contoken to 2 parts otherwise calculation will fail */
/* split contoken to 4 parts to find leading zeroes for C/C++ */
t1 = d2x(c2d(substr(tokn,1,2)));
t2 = d2x(c2d(substr(tokn,3,2)));
t3 = d2x(c2d(substr(tokn,5,2)));
t4 = d2x(c2d(substr(tokn,7,2)));
/* check for x'0' at left nibble of 1st byte so we can print it */
if length(t1) = 3 then t1 = '0' || t1 ;
if length(t2) = 3 then t2 = '0' || t2 ;
if length(t3) = 3 then t3 = '0' || t3 ;
if length(t4) = 3 then t4 = '0' || t4 ;
queue " "
queue " MEMBER " lib1
queue copies('+',60)
queue " "
queue " USER=" user " NAME=" name "TOKEN=" t1 t2 t3 t4 " <===="
queue " "
select;
when env='PROD' & contokp.0 > 0 then
do i1=1 to contokp.0;
queue " prod info for "parm" is: "collp.i1" "contokp.i1 "<===="
if contokp.i1=t1||t2||t3||t4 then
queue " DBRM CONTOKEN MATCHES "collp.i1" contoken <=====X=X=X"
else queue " dbrm contoken DOES NOT match "collp.i1" contoken"
queue " "
end;
when env='DEVL' & contokd.0 > 0 then
do i1=1 to contokd.0;
queue " prod info for "parm" is: "colld.i1" "contokd.i1 "<===="
if contokd.i1=t1||t2||t3||t4 then
queue " DBRM CONTOKEN MATCHES "colld.i1" contoken <=====X=X=X"
else queue " dbrm contoken DOES NOT match "colld.i1" contoken"
queue " "
end;
when env='TEST' & contokt.0 > 0 then
do i1=1 to contokt.0;
queue " prod info for "parm" is: "collt.i1" "contokt.i1 "<===="
if contokt.i1=t1||t2||t3||t4 then
queue " DBRM CONTOKEN MATCHES "collt.i1" contoken <=====X=X=X"
else queue " dbrm contoken DOES NOT match "collt.i1" contoken"
end;
otherwise queue " dbrm "parm" NOT FOUND in catalog <===="
end;
queue " "
queue copies('+',60)
queue " (C/C++)Cload / Cobj Token=" t4 t3 t2 t1
queue " Remeber: Token part that starts with 0 is thrown"
if left(t4,1) <> '0' then invcon = t4 ;
if left(t3,1) <> '0' then invcon = invcon || t3 ;
if left(t2,1) <> '0' then invcon = invcon || t2 ;
if left(t1,1) <> '0' then invcon = invcon || t1 ;
queue " Token in C-language is=" invcon " <=== "
queue " "
i = i + 1 ; /* jump over 2nd line of dbrm */
RETURN
END
if substr(line.i,1,4) = 'DBRM' then
do ;
if sql <> ' ' then call cmd;
num = SUBSTR(line.i,15,2) ;
byte = SUBSTR(line.i,23,2) ;
stmt = SUBSTR(line.i,25,56);
stmtn1 = C2D(num);
byten1 = C2D(byte);
queue copies('+',60);
queue ' '
queue 'STMT_no: ' stmtn1 ' LENGTH: ' byten1
queue copies('=',60)
sql = strip(stmt) ;
end ;
else sql = sql||line.i ; /* concat next line */
RETURN
/****************************************************************/
CMD:
sql=strip(sql,t,' ');
/* change ' . ' to '.' */
bdb=pos(' . ',sql);
do while bdb > 0;
sql=substr(sql,1,bdb-1)||'.'||substr(sql,bdb+3);
bdb=pos(' . ',sql);
end;
hv=1;
/*sql=translate(sql,'40'x,'00'x);*/
/* scan sql and replace ': H' with correct host-var(including length)*/
if pos(' CURSOR ',sql)>0 | ,
pos('SELECT',sql)=1 ,
then
do;
call hostvar sql;
rplc=0;
p2=pos(': H',sql); /* look for host-vars to rebuild command */
do p1=1 to hv while(p2 <> 0);
sql=substr(sql,1,p2-1)||hvnm.p1||substr(sql,p2+4);
p2=pos(': H',sql);
rplc=1;
end;
/*if rplc=1 then
sql=left(sql,lastpos(varname,sql)+length(hvnm.hv));*/
/* end-scan */
end;
k = 0 ;
/* queue print lines */
do while length(sql) > 0;
k=k+1;
lin.k=substr(sql,1,80);
el=lastpos(' ',lin.k);
if el=0 then el=80;
lin.k=substr(lin.k,1,el);
sql=substr(sql,el+1);
queue lin.k;
end;
sql = '' ;
RETURN
/****************************************************************/
HOSTVAR:
arg sql1
p1 = pos('80'x,sql1);
do hv=1 by 1 while p1 > 0 ; /* collect all host-var info */
sql1 = substr(sql1,p1+1); /* get only host-vars name part of sql */
p1 = pos('80'x,sql1); /* get next hv position */
if p1=1 then /* consecutive '8080'x - ignore */
do while p1=1;
sql1=substr(sql1,2);
p1 = pos('80'x,sql1); /* get next hv position */
end;
var.hv = substr(sql1,1,p1+1);
if p1=0 then var.hv = substr(sql1,1); /* last one */
end;
hv=hv-1;
do x1=1 to hv while(hv>0);
varname=substr(var.x1,16);
not00=1;
do z=1 to length(varname) while not00<>0 ;
if substr(varname,z,1)='00'x then
do;
not00=0;
varname=substr(varname,1,z-1);
end;
end;
varlen=substr(var.x1,3,6); /* jump over x'0005' */
vd=c2x(varlen);
vd=strip(vd,l,0);
if left(vd,2)='40' then vd=0;
if vd<>0 then vd=x2d(vd);
hvnm.x1=' 'varname '('vd') '
end;
RETURN
/****************************************************************/
that rexx compares package contoken with the DBRM, not with the loadmodule.
select substr(hex(contoken),9,8) !! substr(hex(contoken),1,8) from sysibm.syspackage where name = ?
gives you a hex string which should be in the loadmodule
fe. : when hex(contoken) = '1900C6161E8DC7EC'
then Find x'1E8DC7EC1900C616' in the loadmodule
This does not guarantee
- the use of the correct plan/collection during execution
- the use of the correct loadlibrary during execution
- if a NEW COPY has been executed in cics
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
