After writing your SQL procedure, you must issue the CREATE PROCEDURE statement as described in Issuing CREATE PROCEDURE Statements. In certain situations, DB2 may return an error in response to your CREATE PROCEDURE statement. To retrieve more information on the error returned by DB2, including an explanation and suggestions for correcting the error, issue the following command at the CLP:
db2 "? error-code"
where error-code represents the SQLCODE or SQLSTATE returned by the error. For example, if your CREATE PROCEDURE statement returns an error with SQLCODE "SQL0469N" ("Parameter mode is not valid"), issue the following command:
db2 "? SQL0469"
DB2 returns the following message:
Explanation: One of the following errors occurred:
o a parameter in an SQL procedure is declared as OUT and is
used as input in the procedure body
o a parameter in an SQL procedure is declared as IN and is
modified in the procedure body.
User Response: Change the attribute of the parameter to INOUT,
or change the use of the parameter within the procedure.
Once you display the message, try modifying your SQL procedure following the suggestions in the "User Response" section.
When you issue a CREATE PROCEDURE statement for an SQL procedure, DB2 may accept the syntax of the SQL procedure body but fail to create the SQL procedure at the precompile or compile stage. In these situations, DB2 normally creates a log file that contains the error messages. This log file, and other intermediate files, are described in Debugging SQL Procedures Using Intermediate Files.
To retrieve the error messages generated by DB2 and the C compiler for an SQL procedure, display the message log file in the following directory on your database server:
where $DB2PATH represents the location of the instance directory, $DATABASE represents the database name, and $SCHEMA represents the schema name used to create the SQL procedure.
where %DB2PATH% represents the location of the instance directory, %DB% represents the database name, and %SCHEMA% represents the schema name used to create the SQL procedure.
You can also issue a CALL statement in an application to call the sample stored procedure db2udp!get_error_messages using the following syntax:
CALL db2udp!get_error_messages(schema-name, file-name, message-text)
where schema-name is an input parameter representing the schema of the SQL procedure, file-name is an input parameter representing the generated file name for the SQL procedure, and message-text is an output parameter that returns the message text in the message log file.
For example, you could use the following Java application to display the error messages for an SQL procedure:
public static String getErrorMessages(Connection con,
String procschema, String filename) throws Exception
{
String filecontents = null;
// prepare the CALL statement
CallableStatement stmt = null;
try
{
String sql = "Call db2udp!get_error_messages(?, ?, ?) ";
stmt = con.prepareCall (sql);
// set all parameters (input and output)
stmt.registerOutParameter( 3, java.sql.Types.LONGVARCHAR );
stmt.setString( 1, procschema );
stmt.setString( 2, filename );
// call the stored procedure
boolean isrs = stmt.execute();
filecontents = stmt.getString(3);
System.out.println("SQL Procedure - getErrorMessages "
+ filecontents);
return filecontents;
}
catch (Exception e) { throw e; }
finally
{
if (stmt != null) stmt.close();
}
}
You could use the following C application to display the error messages for an SQL procedure:
int getErrors(char inputSchema[9], char inputFilename[9],
char outputFilecontents[32000])
{
EXEC SQL BEGIN DECLARE SECTION;
char procschema[100] = "";
char filename[100] = "";
char filecontents[32000] = "";
EXEC SQL END DECLARE SECTION;
strcpy (procschema, inputSchema);
strcpy (filename, inputFilename);
EXEC SQL CALL "db2udp!get_error_messages"
(:procschema, :filename, :filecontents);
if ( sqlca.sqlcode != 0 )
{
printf("Call failed. Code: %d\n", sqlca.sqlcode);
return 1;
}
else
{
printf("\nSQL Procedure - getErrors:\n%s\n", filecontents);
}
strcpy (outputFilecontents, filecontents);
return 0;
}
| Note: | Before you can display the error messages for an SQL procedure that DB2
failed to create, you must know both the procedure name and the generated file
name of the SQL procedure. If the procedure schema name is not issued
as part of the CREATE PROCEDURE statement, DB2 uses the value of the CURRENT
SCHEMA special register. To display the value of the CURRENT SCHEMA
special register, issue the following statement at the CLP:
VALUES CURRENT SCHEMA |
When you issue a CREATE PROCEDURE statement for an SQL procedure, and DB2 accepts the syntax of the SQL procedure body, DB2 uses a number of intermediate files to create the SQL procedure. After DB2 successfully creates an SQL procedure, it normally removes the intermediate files to conserve system resources. If DB2 accepts the CREATE PROCEDURE syntax, but fails to create an SQL procedure, it retains a log file that tracks the precompile, bind, and compile stages of the CREATE PROCEDURE process.
On UNIX systems, DB2 uses the following base directory to keep intermediate files: instance/function/routine/sqlproc/dbAlias/schema, where instance represents the path of the DB2 instance, dbAlias represents the database alias, and schema represents the schema with which the CREATE PROCEDURE statement was issued.
On OS/2 and Windows 32-bit operating systems, DB2 uses the following base directory to keep intermediate files: instance\function\routine\sqlproc\dbAlias\schema, where instance represents the path of the DB2 instance, dbAlias represents the database alias, and schema represents the schema with which the CREATE PROCEDURE statement was issued.
If the SQL procedure was created successfully, but does not return the expected results from your CALL statements, you may want to examine the intermediate files. To prevent DB2 from removing the intermediate files, set the DB2_SQLROUTINE_KEEP_FILES DB2 registry variable to "yes" using the following command:
db2set DB2_SQLROUTINE_KEEP_FILES="yes"
Before DB2 can use the new value of the registry variable, you must restart the database.