SQLPRIMARYKEYS corrupted

**Dave Hughes** · Aug 31 '07, 04:35 PM

Re: SQLPRIMARYKEYS corrupted

On Fri, 31 Aug 2007 00:15:53 -0700, Otto Carl Marte scribbled:

The SYSIBM.SQLPRIMA RYKEYS stored procedure is corrupt in one of my
database. How do I go about re-creating this? I have tried rebinding
"call sysproc.rebind_ routine_package ('P', 'SYSIBM.SQLPRIM ARYKEYS',
'ANY')" but get a nasty -443 error. Also, the SQLPRIMARYKEYS entry does
not exist in my SYSCAT.ROUTINED EP table anymore. Is there any way of
recovering from corrupted system object?

SYSIBM.SQLPRIMA RYKEYS isn't a stored procedure (at least, not in DB2 v8
for LUW) - it's a view which I think is intended for use by the DB2 ODBC
driver. Hence, rebind isn't going to help and SQLPRIMARYKEYS shouldn't
appear in SYSCAT.ROUTINED EP.

What exactly do you mean by "corrupted" ? Is it still there? Can you query
it? What (if any) error message has told you it's corrupted?

Cheers,

Dave.

**Otto Carl Marte** · Sep 2 '07, 03:55 PM

Re: SQLPRIMARYKEYS corrupted

Although there is a view called SYSIBM.SQLPRIMA RYKEYS there is also a
routine called SYSIBM.SQLPRIMA RYKEYS on both Db2 v8 and DB2 v9. This
stored procedure has the following signature
SYSIBM.SQLPRIMA RYKEYS(catalog_ name , schema_name, table_name, options)
which is used by the DB2 ODBC Driver.

Here is the ODBC trace:

SQLPrimaryKeysW ( hStmt=1:2, szTableQualifie r="REALTIME",
cbTableQualifie r=-3, szTableOwner="R EALTIME", cbTableOwner=-3,
szTableName="TA SKS", cbTableName=-3 )
---Time elapsed - +1.960000E-004 seconds
( StmtOut="CALL SYSIBM.SQLPRIMA RYKEYS(?,?,?,?) " )
( Package="SYSSH2 00 ", Section=5 )
( Row=1, iPar=1, fCType=SQL_C_CH AR, rgbValue=<NULL pointer>,
pcbValue=-1, piIndicatorPtr=-1 )
( Row=1, iPar=2, fCType=SQL_C_CH AR, rgbValue="REALT IME" -
x'5245414C54494 D45', pcbValue=8, piIndicatorPtr= 8 )
( Row=1, iPar=3, fCType=SQL_C_CH AR, rgbValue="TASKS " - x'5441534B53',
pcbValue=5, piIndicatorPtr= 5 )
( Row=1, iPar=4, fCType=SQL_C_CH AR, rgbValue="DATAT YPE='ODBC';" -
x'4441544154595 0453D274F444243 273B', pcbValue=16, piIndicatorPtr= 16 )
( ROLLBACK REQUESTED=1 )
( ROLLBACK REPLY RECEIEVED=1 )

SQLPrimaryKeysW ( )
<--- SQL_ERROR Time elapsed - +3.717500E-002 seconds

SQLGetDiagField W( fHandleType=SQL _HANDLE_STMT, hHandle=1:2,
iRecNumber=1, fDiagIdentifier =SQL_DIAG_SQLST ATE, pDiagInfo=&0012 ea38,
cbDiagInfoMax=1 2, pcbDiagInfo=<NU LL pointer)
---Time elapsed - +1.640000E-004 seconds

SQLGetDiagField W( pDiagInfo="3855 3" )
<--- SQL_SUCCESS Time elapsed - +1.645000E-003 seconds

SQLGetDiagField W( fHandleType=SQL _HANDLE_STMT, hHandle=1:2,
iRecNumber=2, fDiagIdentifier =SQL_DIAG_SQLST ATE, pDiagInfo=&0012 ea38,
cbDiagInfoMax=1 2, pcbDiagInfo=<NU LL pointer)
---Time elapsed - +1.570000E-004 seconds

SQLGetDiagField W( )
<--- SQL_NO_DATA_FOU ND Time elapsed - +1.471000E-003 seconds

SQLGetDiagRecW( fHandleType=SQL _HANDLE_STMT, hHandle=1:2,
iRecNumber=1, pszSqlState=&00 12ea6c, pfNativeError=& 0012e650,
pszErrorMsg=&00 12e66c, cbErrorMsgMax=5 12, pcbErrorMsg=&00 12e654 )
---Time elapsed - +1.930000E-004 seconds
( iRowNumber=-1, iColumnNumber=-2 )

SQLGetDiagRecW( pszSqlState="38 553", pfNativeError=-443,
pszErrorMsg="[IBM][CLI Driver][DB2/AIX64] SQL0443N Routine
"SYSIBM.SQLPRIM ARYKEYS" (specific name "PRIMARYKEY S") has returned an
error SQLSTATE with diagnostic text "SYSIBM:CLI :-805". SQLSTATE=38553

", pcbErrorMsg=189 )
<--- SQL_SUCCESS Time elapsed - +3.725000E-003 seconds

As you can see its the error -805 i.e Package not found. Hence my
question "how do I recreate this stored procedure?" Is it possible to
recreate system procedures?

**Jean-Marc Blaise** · Sep 2 '07, 06:35 PM

Re: SQLPRIMARYKEYS corrupted

Hi Otto,

Have you bound the db2schema.bnd to your db ?

hope this helps,

JM

"Otto Carl Marte" <Otto.Marte@gma il.comwrote in message
news:1188748296 .353002.135890@ k79g2000hse.goo glegroups.com.. .

Although there is a view called SYSIBM.SQLPRIMA RYKEYS there is also a
routine called SYSIBM.SQLPRIMA RYKEYS on both Db2 v8 and DB2 v9. This
stored procedure has the following signature
SYSIBM.SQLPRIMA RYKEYS(catalog_ name , schema_name, table_name, options)
which is used by the DB2 ODBC Driver.
>
Here is the ODBC trace:
>
SQLPrimaryKeysW ( hStmt=1:2, szTableQualifie r="REALTIME",
cbTableQualifie r=-3, szTableOwner="R EALTIME", cbTableOwner=-3,
szTableName="TA SKS", cbTableName=-3 )
---Time elapsed - +1.960000E-004 seconds
( StmtOut="CALL SYSIBM.SQLPRIMA RYKEYS(?,?,?,?) " )
( Package="SYSSH2 00 ", Section=5 )
( Row=1, iPar=1, fCType=SQL_C_CH AR, rgbValue=<NULL pointer>,
pcbValue=-1, piIndicatorPtr=-1 )
( Row=1, iPar=2, fCType=SQL_C_CH AR, rgbValue="REALT IME" -
x'5245414C54494 D45', pcbValue=8, piIndicatorPtr= 8 )
( Row=1, iPar=3, fCType=SQL_C_CH AR, rgbValue="TASKS " - x'5441534B53',
pcbValue=5, piIndicatorPtr= 5 )
( Row=1, iPar=4, fCType=SQL_C_CH AR, rgbValue="DATAT YPE='ODBC';" -
x'4441544154595 0453D274F444243 273B', pcbValue=16, piIndicatorPtr= 16 )
( ROLLBACK REQUESTED=1 )
( ROLLBACK REPLY RECEIEVED=1 )
>
SQLPrimaryKeysW ( )
<--- SQL_ERROR Time elapsed - +3.717500E-002 seconds
>
SQLGetDiagField W( fHandleType=SQL _HANDLE_STMT, hHandle=1:2,
iRecNumber=1, fDiagIdentifier =SQL_DIAG_SQLST ATE, pDiagInfo=&0012 ea38,
cbDiagInfoMax=1 2, pcbDiagInfo=<NU LL pointer)
---Time elapsed - +1.640000E-004 seconds
>
SQLGetDiagField W( pDiagInfo="3855 3" )
<--- SQL_SUCCESS Time elapsed - +1.645000E-003 seconds
>
SQLGetDiagField W( fHandleType=SQL _HANDLE_STMT, hHandle=1:2,
iRecNumber=2, fDiagIdentifier =SQL_DIAG_SQLST ATE, pDiagInfo=&0012 ea38,
cbDiagInfoMax=1 2, pcbDiagInfo=<NU LL pointer)
---Time elapsed - +1.570000E-004 seconds
>
SQLGetDiagField W( )
<--- SQL_NO_DATA_FOU ND Time elapsed - +1.471000E-003 seconds
>
SQLGetDiagRecW( fHandleType=SQL _HANDLE_STMT, hHandle=1:2,
iRecNumber=1, pszSqlState=&00 12ea6c, pfNativeError=& 0012e650,
pszErrorMsg=&00 12e66c, cbErrorMsgMax=5 12, pcbErrorMsg=&00 12e654 )
---Time elapsed - +1.930000E-004 seconds
( iRowNumber=-1, iColumnNumber=-2 )
>
SQLGetDiagRecW( pszSqlState="38 553", pfNativeError=-443,
pszErrorMsg="[IBM][CLI Driver][DB2/AIX64] SQL0443N Routine
"SYSIBM.SQLPRIM ARYKEYS" (specific name "PRIMARYKEY S") has returned an
error SQLSTATE with diagnostic text "SYSIBM:CLI :-805". SQLSTATE=38553
>
", pcbErrorMsg=189 )
<--- SQL_SUCCESS Time elapsed - +3.725000E-003 seconds
>
As you can see its the error -805 i.e Package not found. Hence my
question "how do I recreate this stored procedure?" Is it possible to
recreate system procedures?
>

**Jean-Marc Blaise** · Sep 2 '07, 06:35 PM

Re: SQLPRIMARYKEYS corrupted

.... also from the Configuration Assistant, you could bind CLI/ODBC utilities

LINE MESSAGES FOR db2clipk.bnd
------ --------------------------------------------------------------------
SQL0061W The binder is in progress.
SQL0091N Binding was ended with "0" errors and "0" warnings.

LINE MESSAGES FOR db2clist.bnd
------ --------------------------------------------------------------------
SQL0061W The binder is in progress.
SQL0091N Binding was ended with "0" errors and "0" warnings.

Bind completed successfully.

"Jean-Marc Blaise" <db2@bestdb.com wrote in message
news:46db005e$0 $30655$426a34cc @news.free.fr.. .

Hi Otto,
>
Have you bound the db2schema.bnd to your db ?
>
hope this helps,
>
JM
>
"Otto Carl Marte" <Otto.Marte@gma il.comwrote in message
news:1188748296 .353002.135890@ k79g2000hse.goo glegroups.com.. .

>Although there is a view called SYSIBM.SQLPRIMA RYKEYS there is also a
>routine called SYSIBM.SQLPRIMA RYKEYS on both Db2 v8 and DB2 v9. This
>stored procedure has the following signature
>SYSIBM.SQLPRIM ARYKEYS(catalog _name , schema_name, table_name, options)
>which is used by the DB2 ODBC Driver.
>>
>Here is the ODBC trace:
>>
>SQLPrimaryKeys W( hStmt=1:2, szTableQualifie r="REALTIME",
>cbTableQualifi er=-3, szTableOwner="R EALTIME", cbTableOwner=-3,
>szTableName="T ASKS", cbTableName=-3 )
> ---Time elapsed - +1.960000E-004 seconds
>( StmtOut="CALL SYSIBM.SQLPRIMA RYKEYS(?,?,?,?) " )
>( Package="SYSSH2 00 ", Section=5 )
>( Row=1, iPar=1, fCType=SQL_C_CH AR, rgbValue=<NULL pointer>,
>pcbValue=-1, piIndicatorPtr=-1 )
>( Row=1, iPar=2, fCType=SQL_C_CH AR, rgbValue="REALT IME" -
>x'5245414C5449 4D45', pcbValue=8, piIndicatorPtr= 8 )
>( Row=1, iPar=3, fCType=SQL_C_CH AR, rgbValue="TASKS " - x'5441534B53',
>pcbValue=5, piIndicatorPtr= 5 )
>( Row=1, iPar=4, fCType=SQL_C_CH AR, rgbValue="DATAT YPE='ODBC';" -
>x'444154415459 50453D274F44424 3273B', pcbValue=16, piIndicatorPtr= 16 )
>( ROLLBACK REQUESTED=1 )
>( ROLLBACK REPLY RECEIEVED=1 )
>>
>SQLPrimaryKeys W( )
> <--- SQL_ERROR Time elapsed - +3.717500E-002 seconds
>>
>SQLGetDiagFiel dW( fHandleType=SQL _HANDLE_STMT, hHandle=1:2,
>iRecNumber=1 , fDiagIdentifier =SQL_DIAG_SQLST ATE, pDiagInfo=&0012 ea38,
>cbDiagInfoMax= 12, pcbDiagInfo=<NU LL pointer)
> ---Time elapsed - +1.640000E-004 seconds
>>
>SQLGetDiagFiel dW( pDiagInfo="3855 3" )
> <--- SQL_SUCCESS Time elapsed - +1.645000E-003 seconds
>>
>SQLGetDiagFiel dW( fHandleType=SQL _HANDLE_STMT, hHandle=1:2,
>iRecNumber=2 , fDiagIdentifier =SQL_DIAG_SQLST ATE, pDiagInfo=&0012 ea38,
>cbDiagInfoMax= 12, pcbDiagInfo=<NU LL pointer)
> ---Time elapsed - +1.570000E-004 seconds
>>
>SQLGetDiagFiel dW( )
> <--- SQL_NO_DATA_FOU ND Time elapsed - +1.471000E-003 seconds
>>
>SQLGetDiagRecW ( fHandleType=SQL _HANDLE_STMT, hHandle=1:2,
>iRecNumber=1 , pszSqlState=&00 12ea6c, pfNativeError=& 0012e650,
>pszErrorMsg=&0 012e66c, cbErrorMsgMax=5 12, pcbErrorMsg=&00 12e654 )
> ---Time elapsed - +1.930000E-004 seconds
>( iRowNumber=-1, iColumnNumber=-2 )
>>
>SQLGetDiagRecW ( pszSqlState="38 553", pfNativeError=-443,
>pszErrorMsg= "[IBM][CLI Driver][DB2/AIX64] SQL0443N Routine
>"SYSIBM.SQLPRI MARYKEYS" (specific name "PRIMARYKEY S") has returned an
>error SQLSTATE with diagnostic text "SYSIBM:CLI :-805". SQLSTATE=38553
>>
>", pcbErrorMsg=189 )
> <--- SQL_SUCCESS Time elapsed - +3.725000E-003 seconds
>>
>As you can see its the error -805 i.e Package not found. Hence my
>question "how do I recreate this stored procedure?" Is it possible to
>recreate system procedures?
>>

>
>

**Dave Hughes** · Sep 3 '07, 10:35 AM

Re: SQLPRIMARYKEYS corrupted

On Sun, 02 Sep 2007 08:51:36 -0700, Otto Carl Marte scribbled:

Although there is a view called SYSIBM.SQLPRIMA RYKEYS there is also a
routine called SYSIBM.SQLPRIMA RYKEYS on both Db2 v8 and DB2 v9. This
stored procedure has the following signature
SYSIBM.SQLPRIMA RYKEYS(catalog_ name , schema_name, table_name, options)
which is used by the DB2 ODBC Driver.

My apologies - you're absolutely right (for some reason I had a notion
there weren't any routines with an "SQL" prefix in the system catalog,
just views).

Did Jean-Marc's suggestion regarding db2schema.bnd help?

Another thing you might try if it's still missing is to run db2updv8 (or
v9) against the database (the command is meant to update the system
catalogs with new objects after a fixpak installation, so I guess there's
a slim chance it might rebuild the routine if it finds it's missing).

Cheers,

Dave.

**Otto Carl Marte** · Sep 3 '07, 02:55 PM

Re: SQLPRIMARYKEYS corrupted

Hi,

Thanks for all the suggestions. But sadly, it hasn't solved my
problem.

issuing this:

call sysibm.sqlprima rykeys(null, 'REALTIME', 'TASKS', null)

gives me:

DB2 SQL error: SQLCODE: -443, SQLSTATE: 38553, SQLERRMC:
SYSIBM.SQLPRIMA RYKEYS;PRIMARYK EYS;SYSIBM:CLI:-805
Message: Routine "SYSIBM.SQLPRIM ARYKEYS" (specific name
"PRIMARYKEY S") has returned an error SQLSTATE with diagnostic text
"SYSIBM:CLI :-805".

**Knut Stolze** · Sep 3 '07, 06:05 PM

Re: SQLPRIMARYKEYS corrupted

Otto Carl Marte wrote:

Hi,
>
Thanks for all the suggestions. But sadly, it hasn't solved my
problem.
>
issuing this:
>
call sysibm.sqlprima rykeys(null, 'REALTIME', 'TASKS', null)
>
gives me:
>
DB2 SQL error: SQLCODE: -443, SQLSTATE: 38553, SQLERRMC:
SYSIBM.SQLPRIMA RYKEYS;PRIMARYK EYS;SYSIBM:CLI:-805
Message: Routine "SYSIBM.SQLPRIM ARYKEYS" (specific name
"PRIMARYKEY S") has returned an error SQLSTATE with diagnostic text
"SYSIBM:CLI :-805".

You could try to set the DIAGLEVEL to 4, rerun the CALL statement and check
if something interesting got written to the db2diag.log.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

**Otto Carl Marte** · Sep 4 '07, 01:35 PM

Re: SQLPRIMARYKEYS corrupted

Is there no way to recreate the stored procs in the SYSIBM schema? Is
there no tools/mechanism for doing this?

**Ian** · Sep 4 '07, 04:05 PM

Re: SQLPRIMARYKEYS corrupted

Otto Carl Marte wrote:

Is there no way to recreate the stored procs in the SYSIBM schema? Is
there no tools/mechanism for doing this?

Have you tried rebinding utilities and running 'db2updv8' (or db2updv9,
if you're on DB2 9) ?

**Jean-Marc Blaise** · Sep 4 '07, 07:15 PM

Re: SQLPRIMARYKEYS corrupted

Hi Otto,

If you bind db2schema.bnd, this will recreate it.
select * from syscat.packaged ep | find "SQLTABLES" ==gives you the package
name
db2bfd -b %SQLLIB%\bnd\db 2schema.bnd will show you that this corresponds to
the package name you had above.

HTH,

JM

"Otto Carl Marte" <Otto.Marte@gma il.comwrote in message
news:1188912458 .825971.280330@ y42g2000hsy.goo glegroups.com.. .

Is there no way to recreate the stored procs in the SYSIBM schema? Is
there no tools/mechanism for doing this?
>
>
>

**Otto Carl Marte** · Sep 5 '07, 04:05 PM

Re: SQLPRIMARYKEYS corrupted

Thanks for the suggestions :-)
I have tried the db2updv9 without success.
I don't see how the bind will help.
Executing:

call sysibm.sqlprima rykeys(null, 'REALTIME', 'TASKS', null)

from the CLP on AIX causes the same problem.

SQLPRIMARYKEYS corrupted

SQLPRIMARYKEYS corrupted

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment