SQLCODE -331

Recently I altered a tablespace CCSID from EBCDIC to UNICODE. This tablespace had a PLAN_TABLE in it. After altering the tablespace, I couldn't retrieve certain rows from this PLAN_TABLE. I started getting SQLCODE -331. The message appeared like this:

"CHARACTER CONVERSION CANNOT BE PERFORMED BECAUSE A STRING, POSITION43 CANNNOT BE CONVERTED FROM 367 TO 37, REASON 12"

Position 43 refers to column IBM_REQUIRED_DATA in the PLAN_TABLE. As a short term solution, I spaced out the value in this column and it fixed it. However, it kept appearing for different programs/queries. So, I altered the table column attribute for IBM_REQUIRED_DATA from "FOR SBCS DATA" to "FOR BIT DATA". It fixed the problem.

SQLCODE -518

The reason mentioned in the DB2 manual for SQLCODE -518 is "THE EXECUTE STATEMENT DOES NOT IDENTIFY A VALID PREPARED STATEMENT"

You may get this SQLCODE for many reasons. I don't know them all. For me this is what happened. When I tried to connect to a remote DB2 database (on LUW) from a COBOL program on the mainframe, the CONNECT was successful. However when I tried to run an SQL on that database I got this SQLCODE.

After researching for a while, I found that I had an existing SQL statement within EXEC SQL and END-EXEC. That worked good. But when I commented out one of WHERE clause statements (using * at column 7), it gave me this error. I moved the commented statement to the line after END-EXEC and it started working again.

Bottom line is DB2 doesn't like COBOL-like commented code inside EXEC SQL and END-EXEC when you are trying to run the SQL at a remote DB2 database.

SQLCODE -804

SQLCODE -804 means something messed up with the SQLDA. Since SQLDA is internally created by DB2 for static COBOL programs, it is possible for a COBOL subscript to overwrite SQLDA statements which are inserted by DB2 at the end of the Working Storage section.

If your programmer complains about this sqlcode, have him/her increase the table size (increase the 'TIMES' parameter value) and recompile the program.

SYSIBM.SQT* tables and SQLCODE -526

This is going to be a small post ...

If you plan to use the IBM Data Server Driver for JDBC and SQLJ or the DB2 ODBC driver, you must create the SYSIBM.SQT* Tables & SYSIBM.SQL* Stored Procs that provide support for those drivers.

GRANT ALL access to PUBLIC to these tables. Trying to GRANT individual access like GRANT SELECT won't work (you'll get SQLCODE -526).

SQLCODE -904 with Reason Code "00D70024"

When you try to access or drop a table, you may get this error if the underlying VSAM data set for the table does not exist. If you are dropping the table, try dropping the tablespace instead (if there is just one table in the tablespace). If you are trying to browse the data, you are out of luck. Try recreating the table from another subsystem and load the data from there. Or, do a LOAD REPLACE with DUMMY SYSREC (it will create the underlying datasets). This will allow you access the table, but the table will be empty.

DB2 SQLCODE -741

I was installing a DB2 vendor tool yesterday. Part of the install was to create a new temporary database. When I ran the following DDL,

CREATE DATABASE database
              STOGROUP ASTERISK
              BUFFERPOOL BP2
              INDEXBP BP2
              AS TEMP

I got this message:

DSNT408I SQLCODE = -741, ERROR:  A TEMP DATABASE IS ALREADY DEFINED FOR
       MEMBER ssid

The problem here is, I was trying to define a temporary database whereas a temporary database already exists for that subsystem. ONLY ONE TEMPORARY DATABASE is allowed per subsystem or data sharing member.

I learnt a lesson !!

DB2 SQLCODE -497

If you get an SQLCODE -497 while trying to run a QMF PROC, read this:

SQLCODE -497 means "THE MAXIMUM LIMIT OF INTERNAL IDENTIFIERS HAS BEEN
EXCEEDED FOR DATABASE database"

You need to run the MODIFY utility with RECOVERY option to fix this issue.

The MODIFY online utility with the RECOVERY option deletes records from the SYSIBM.SYSCOPY catalog table, related log records from the SYSIBM.SYSLGRNX directory table, and entries from the DBD. You can remove records that were written before a specific date or you can remove records of a specific age. You can delete records for an entire table space, partition, or data set.

Sample SYSIN for MODIFY:

MODIFY RECOVERY TABLESPACE database.tablespace DELETE AGE(365)

DB2 SQLCODE=-950

We use BCV split (Business Continuance Volume) technology to clone our Peoplesoft DB2 production subsystem to DB2 test subsystem. After we cloned one of our subsystems, we noticed that our programmers were not able to access QMF. They started getting the error message:

"DSQ16950 The location name is not known to the local DB2 subsystem.".

This was the error message from QMF. The actual error message issued by DB2 was "SQLCODE -950" which means "THE LOCATION NAME SPECIFIED IN THE CONNECT STATEMENT IS INVALID OR NOT LISTED IN THE COMMUNICATIONS DATABASE".

When you get this message check SYSIBM.LOCATIONS to see if a row is there for the DB2 subsystem it is complaining about. If there is one and the values are correct, check the application PLAN. In our case we checked QMF PLAN.

Check if you have the right value for CURRENTSERVER in your BIND PLAN. If you don't have the correct value, rebind the plan with correct value for this parameter. If you are on a local server (on a local LPAR), just remove this parameter. Removing this parameter and rebinding the plan fixed our issue with QMF.