By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,610 Members | 1,677 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,610 IT Pros & Developers. It's quick & easy.

SQLPRIMARYKEYS corrupted

P: n/a
The SYSIBM.SQLPRIMARYKEYS 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.SQLPRIMARYKEYS',
'ANY')" but get a nasty -443 error. Also, the SQLPRIMARYKEYS entry
does not exist in my SYSCAT.ROUTINEDEP table anymore.
Is there any way of recovering from corrupted system object?

Aug 31 '07 #1
Share this Question
Share on Google+
11 Replies


P: n/a
On Fri, 31 Aug 2007 00:15:53 -0700, Otto Carl Marte scribbled:
The SYSIBM.SQLPRIMARYKEYS 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.SQLPRIMARYKEYS',
'ANY')" but get a nasty -443 error. Also, the SQLPRIMARYKEYS entry does
not exist in my SYSCAT.ROUTINEDEP table anymore. Is there any way of
recovering from corrupted system object?
SYSIBM.SQLPRIMARYKEYS 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.ROUTINEDEP.

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.
Aug 31 '07 #2

P: n/a
Although there is a view called SYSIBM.SQLPRIMARYKEYS there is also a
routine called SYSIBM.SQLPRIMARYKEYS on both Db2 v8 and DB2 v9. This
stored procedure has the following signature
SYSIBM.SQLPRIMARYKEYS(catalog_name , schema_name, table_name, options)
which is used by the DB2 ODBC Driver.

Here is the ODBC trace:

SQLPrimaryKeysW( hStmt=1:2, szTableQualifier="REALTIME",
cbTableQualifier=-3, szTableOwner="REALTIME", cbTableOwner=-3,
szTableName="TASKS", cbTableName=-3 )
---Time elapsed - +1.960000E-004 seconds
( StmtOut="CALL SYSIBM.SQLPRIMARYKEYS(?,?,?,?)" )
( Package="SYSSH200 ", Section=5 )
( Row=1, iPar=1, fCType=SQL_C_CHAR, rgbValue=<NULL pointer>,
pcbValue=-1, piIndicatorPtr=-1 )
( Row=1, iPar=2, fCType=SQL_C_CHAR, rgbValue="REALTIME" -
x'5245414C54494D45', pcbValue=8, piIndicatorPtr=8 )
( Row=1, iPar=3, fCType=SQL_C_CHAR, rgbValue="TASKS" - x'5441534B53',
pcbValue=5, piIndicatorPtr=5 )
( Row=1, iPar=4, fCType=SQL_C_CHAR, rgbValue="DATATYPE='ODBC';" -
x'44415441545950453D274F444243273B', pcbValue=16, piIndicatorPtr=16 )
( ROLLBACK REQUESTED=1 )
( ROLLBACK REPLY RECEIEVED=1 )

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

SQLGetDiagFieldW( fHandleType=SQL_HANDLE_STMT, hHandle=1:2,
iRecNumber=1, fDiagIdentifier=SQL_DIAG_SQLSTATE, pDiagInfo=&0012ea38,
cbDiagInfoMax=12, pcbDiagInfo=<NULL pointer)
---Time elapsed - +1.640000E-004 seconds

SQLGetDiagFieldW( pDiagInfo="38553" )
<--- SQL_SUCCESS Time elapsed - +1.645000E-003 seconds

SQLGetDiagFieldW( fHandleType=SQL_HANDLE_STMT, hHandle=1:2,
iRecNumber=2, fDiagIdentifier=SQL_DIAG_SQLSTATE, pDiagInfo=&0012ea38,
cbDiagInfoMax=12, pcbDiagInfo=<NULL pointer)
---Time elapsed - +1.570000E-004 seconds

SQLGetDiagFieldW( )
<--- SQL_NO_DATA_FOUND Time elapsed - +1.471000E-003 seconds

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

SQLGetDiagRecW( pszSqlState="38553", pfNativeError=-443,
pszErrorMsg="[IBM][CLI Driver][DB2/AIX64] SQL0443N Routine
"SYSIBM.SQLPRIMARYKEYS" (specific name "PRIMARYKEYS") 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?

Sep 2 '07 #3

P: n/a
Hi Otto,

Have you bound the db2schema.bnd to your db ?

hope this helps,

JM

"Otto Carl Marte" <Ot********@gmail.comwrote in message
news:11**********************@k79g2000hse.googlegr oups.com...
Although there is a view called SYSIBM.SQLPRIMARYKEYS there is also a
routine called SYSIBM.SQLPRIMARYKEYS on both Db2 v8 and DB2 v9. This
stored procedure has the following signature
SYSIBM.SQLPRIMARYKEYS(catalog_name , schema_name, table_name, options)
which is used by the DB2 ODBC Driver.

Here is the ODBC trace:

SQLPrimaryKeysW( hStmt=1:2, szTableQualifier="REALTIME",
cbTableQualifier=-3, szTableOwner="REALTIME", cbTableOwner=-3,
szTableName="TASKS", cbTableName=-3 )
---Time elapsed - +1.960000E-004 seconds
( StmtOut="CALL SYSIBM.SQLPRIMARYKEYS(?,?,?,?)" )
( Package="SYSSH200 ", Section=5 )
( Row=1, iPar=1, fCType=SQL_C_CHAR, rgbValue=<NULL pointer>,
pcbValue=-1, piIndicatorPtr=-1 )
( Row=1, iPar=2, fCType=SQL_C_CHAR, rgbValue="REALTIME" -
x'5245414C54494D45', pcbValue=8, piIndicatorPtr=8 )
( Row=1, iPar=3, fCType=SQL_C_CHAR, rgbValue="TASKS" - x'5441534B53',
pcbValue=5, piIndicatorPtr=5 )
( Row=1, iPar=4, fCType=SQL_C_CHAR, rgbValue="DATATYPE='ODBC';" -
x'44415441545950453D274F444243273B', pcbValue=16, piIndicatorPtr=16 )
( ROLLBACK REQUESTED=1 )
( ROLLBACK REPLY RECEIEVED=1 )

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

SQLGetDiagFieldW( fHandleType=SQL_HANDLE_STMT, hHandle=1:2,
iRecNumber=1, fDiagIdentifier=SQL_DIAG_SQLSTATE, pDiagInfo=&0012ea38,
cbDiagInfoMax=12, pcbDiagInfo=<NULL pointer)
---Time elapsed - +1.640000E-004 seconds

SQLGetDiagFieldW( pDiagInfo="38553" )
<--- SQL_SUCCESS Time elapsed - +1.645000E-003 seconds

SQLGetDiagFieldW( fHandleType=SQL_HANDLE_STMT, hHandle=1:2,
iRecNumber=2, fDiagIdentifier=SQL_DIAG_SQLSTATE, pDiagInfo=&0012ea38,
cbDiagInfoMax=12, pcbDiagInfo=<NULL pointer)
---Time elapsed - +1.570000E-004 seconds

SQLGetDiagFieldW( )
<--- SQL_NO_DATA_FOUND Time elapsed - +1.471000E-003 seconds

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

SQLGetDiagRecW( pszSqlState="38553", pfNativeError=-443,
pszErrorMsg="[IBM][CLI Driver][DB2/AIX64] SQL0443N Routine
"SYSIBM.SQLPRIMARYKEYS" (specific name "PRIMARYKEYS") 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?

Sep 2 '07 #4

P: n/a
.... 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" <db*@bestdb.comwrote in message
news:46***********************@news.free.fr...
Hi Otto,

Have you bound the db2schema.bnd to your db ?

hope this helps,

JM

"Otto Carl Marte" <Ot********@gmail.comwrote in message
news:11**********************@k79g2000hse.googlegr oups.com...
>Although there is a view called SYSIBM.SQLPRIMARYKEYS there is also a
routine called SYSIBM.SQLPRIMARYKEYS on both Db2 v8 and DB2 v9. This
stored procedure has the following signature
SYSIBM.SQLPRIMARYKEYS(catalog_name , schema_name, table_name, options)
which is used by the DB2 ODBC Driver.

Here is the ODBC trace:

SQLPrimaryKeysW( hStmt=1:2, szTableQualifier="REALTIME",
cbTableQualifier=-3, szTableOwner="REALTIME", cbTableOwner=-3,
szTableName="TASKS", cbTableName=-3 )
---Time elapsed - +1.960000E-004 seconds
( StmtOut="CALL SYSIBM.SQLPRIMARYKEYS(?,?,?,?)" )
( Package="SYSSH200 ", Section=5 )
( Row=1, iPar=1, fCType=SQL_C_CHAR, rgbValue=<NULL pointer>,
pcbValue=-1, piIndicatorPtr=-1 )
( Row=1, iPar=2, fCType=SQL_C_CHAR, rgbValue="REALTIME" -
x'5245414C54494D45', pcbValue=8, piIndicatorPtr=8 )
( Row=1, iPar=3, fCType=SQL_C_CHAR, rgbValue="TASKS" - x'5441534B53',
pcbValue=5, piIndicatorPtr=5 )
( Row=1, iPar=4, fCType=SQL_C_CHAR, rgbValue="DATATYPE='ODBC';" -
x'44415441545950453D274F444243273B', pcbValue=16, piIndicatorPtr=16 )
( ROLLBACK REQUESTED=1 )
( ROLLBACK REPLY RECEIEVED=1 )

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

SQLGetDiagFieldW( fHandleType=SQL_HANDLE_STMT, hHandle=1:2,
iRecNumber=1, fDiagIdentifier=SQL_DIAG_SQLSTATE, pDiagInfo=&0012ea38,
cbDiagInfoMax=12, pcbDiagInfo=<NULL pointer)
---Time elapsed - +1.640000E-004 seconds

SQLGetDiagFieldW( pDiagInfo="38553" )
<--- SQL_SUCCESS Time elapsed - +1.645000E-003 seconds

SQLGetDiagFieldW( fHandleType=SQL_HANDLE_STMT, hHandle=1:2,
iRecNumber=2, fDiagIdentifier=SQL_DIAG_SQLSTATE, pDiagInfo=&0012ea38,
cbDiagInfoMax=12, pcbDiagInfo=<NULL pointer)
---Time elapsed - +1.570000E-004 seconds

SQLGetDiagFieldW( )
<--- SQL_NO_DATA_FOUND Time elapsed - +1.471000E-003 seconds

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

SQLGetDiagRecW( pszSqlState="38553", pfNativeError=-443,
pszErrorMsg="[IBM][CLI Driver][DB2/AIX64] SQL0443N Routine
"SYSIBM.SQLPRIMARYKEYS" (specific name "PRIMARYKEYS") 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?


Sep 2 '07 #5

P: n/a
On Sun, 02 Sep 2007 08:51:36 -0700, Otto Carl Marte scribbled:
Although there is a view called SYSIBM.SQLPRIMARYKEYS there is also a
routine called SYSIBM.SQLPRIMARYKEYS on both Db2 v8 and DB2 v9. This
stored procedure has the following signature
SYSIBM.SQLPRIMARYKEYS(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.
Sep 3 '07 #6

P: n/a
Hi,

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

issuing this:

call sysibm.sqlprimarykeys(null, 'REALTIME', 'TASKS', null)

gives me:

DB2 SQL error: SQLCODE: -443, SQLSTATE: 38553, SQLERRMC:
SYSIBM.SQLPRIMARYKEYS;PRIMARYKEYS;SYSIBM:CLI:-805
Message: Routine "SYSIBM.SQLPRIMARYKEYS" (specific name
"PRIMARYKEYS") has returned an error SQLSTATE with diagnostic text
"SYSIBM:CLI:-805".

Sep 3 '07 #7

P: n/a
Otto Carl Marte wrote:
Hi,

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

issuing this:

call sysibm.sqlprimarykeys(null, 'REALTIME', 'TASKS', null)

gives me:

DB2 SQL error: SQLCODE: -443, SQLSTATE: 38553, SQLERRMC:
SYSIBM.SQLPRIMARYKEYS;PRIMARYKEYS;SYSIBM:CLI:-805
Message: Routine "SYSIBM.SQLPRIMARYKEYS" (specific name
"PRIMARYKEYS") 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
Sep 3 '07 #8

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

Sep 4 '07 #9

P: n/a
Ian
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) ?
Sep 4 '07 #10

P: n/a
Hi Otto,

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

HTH,

JM

"Otto Carl Marte" <Ot********@gmail.comwrote in message
news:11**********************@y42g2000hsy.googlegr oups.com...
Is there no way to recreate the stored procs in the SYSIBM schema? Is
there no tools/mechanism for doing this?

Sep 4 '07 #11

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

call sysibm.sqlprimarykeys(null, 'REALTIME', 'TASKS', null)

from the CLP on AIX causes the same problem.
Sep 5 '07 #12

This discussion thread is closed

Replies have been disabled for this discussion.