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?
11  4643 
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.
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?
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?
.... 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?
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.
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".
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
Is there no way to recreate the stored procs in the SYSIBM schema? Is
there no tools/mechanism for doing this?
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) ?
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?
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.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: r.e.s. |
last post by:
No matter which site I download it from,
NumTut.tgz appears to be corrupted.
Anyone else having this problem?
Thanks.
--
r.e.s.
|
by: WKC |
last post by:
Recently, one of our database's mdf and ldf was corrupted. We were able to
bring back the database with the capability of importing and querying the
data. However, the data is not the full list. ...
|
by: Anup Jishnu |
last post by:
Hi,
I have installed ASP.Net application on a system.
When accessing the Application from within the LAN, it works fine.
However, when I access the application from the Internet, some pages...
|
by: Bob Hynes |
last post by:
Hi All,
Does anyone know of a place where a corrupted mdb(front-end) can be
sent and have that place be able to tell me what got corrupted within
the db?
Here's the issue;
All pc's are WindowsNT...
|
by: Lee Rouse |
last post by:
Hello all,
This is going to be a rather lengthy "question".
I have an Access 2k database, separated front end/back end. Front end
copies are on about 30 workstations and used frequently during...
|
by: Colleyville Alan |
last post by:
I posted the other day that the Incremental Search box that I had been using
was no longer working properly. When I checked the code against an older
version that was working properly, the changes...
|
by: Leinad Kong |
last post by:
I'm using Access 2002, as front-end and back-end as well:
1) I faced database corrupted problems, when more than 1 user edit
concurrently.
I'm using All-records Locking, and open-exclusively as...
|
by: Lauren Wilson |
last post by:
Well, it has finally happened. We have a five year old app that is
widely distributed. I cannot get an update done because none of the
code modules will open. Access complains that the module...
|
by: advance scout |
last post by:
HELP! My database is suddenly corrupted. My computer was acting funny
(very sluggish) and was shut down. Access had been already been closed down
but computer was acting very slow , so perhaps it...
|
by: Rina0 |
last post by:
Cybersecurity engineering is a specialized field that focuses on the design, development, and implementation of systems, processes, and technologies that protect against cyber threats and...
|
by: linyimin |
last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
|
by: erikbower65 |
last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA:
1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
|
by: kcodez |
last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
|
by: Taofi |
last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same
This are my field names
ID, Budgeted, Actual, Status and Differences
...
|
by: DJRhino1175 |
last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this -
If...
|
by: lllomh |
last post by:
How does React native implement an English player?
|
by: Mushico |
last post by:
How to calculate date of retirement from date of birth
|
by: DJRhino |
last post by:
Was curious if anyone else was having this same issue or not....
I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
| |
