2 Release Information

CURSOR WITH HOLD

DECLARE CURSOR statements can now include the WITH HOLD phrase.

STRSQL on Columns of Type NUMBER

When using STRSQL, an ORA-1457 might occur on columns of type NUMBER where no precision or scale is specified. Access Manager supplies default data types precision and scale, for these situations. Even the use of these data types, however, may not be sufficient, and you could still receive the ORA-1457 error. To check the default data type precision and scale values, use the Oracle Access Manager for AS/400 CHKSQLDFLT command.

The AS/400 is shipped with the following default settings:

Datatype *PKDEC (packed decimal)
Precision 7
Scale   2

For example, when using STRSQL with these settings, a column of type NUMBER with a value of 123456 results in an ORA-1457 error. This error occurs because the setting of 7 for precision with the setting of 2 for scale allows for only five digits to the left of the decimal point (precision - scale = 7 - 2 = 5) To avoid this error, you can change the default data type to *FLOAT or keep the data type as *PKDEC and change the precision to 8 while keeping the default scale of 2. This will allow six digits to the left of the decimal point (precision - scale = 8 - 2 = 6).

General Limitations

Oracle hints cannot be specified in a SQL statement that is explicitly part of any EXEC SQL statement. The IBM preprocessor strips out the Oracle hints, as if they were comments.

If you want Oracle hints in a DELETE, SELECT, or UPDATE statement, then you must:

1. Copy the text of the relevant DELETE, SELECT, or UPDATE statement to a host variable.

2. PREPARE a statement from that host variable.

3. For a SELECT, you must DECLARE a cursor for that prepared statement. Open the cursor, and then FETCH from that cursor.

4. For an UPDATE or DELETE, you must EXECUTE the prepared statement.

Stored Procedure Limitations

The following limitations are known to exist with Oracle stored procedures:

• The TIMESTAMP and TIME data types return the date only.

• You must use the DB2/400 DECLARE PROCEDURE statement in preparation to call an Oracle stored procedure from an AS/400 program.

• The DECLARE PROCEDURE statement must have the same number of arguments as the Oracle PL/SQL procedure. The input and output modes must also be the same.

• The SIMPLE CALL WITH NULLS phrase is not supported. The SIMPLE CALL phrase is supported.

PREPARE with a COMMIT or ROLLBACK Command

When using two-phase commit processing, COMMIT or ROLLBACK commands PREPARED as dynamic SQL statements are rejected with the following error:

SQL0969: Error occurred while passing request to application requester driver program.

The following information is available in the job log:

ARDARPS: cannot PREPARE a COMMIT or ROLLBACK

For additional information on two-phase commit processing, refer to Chapter 8, "Using Access Manager".

RDBCNNMTH Parameter set to *DUW

Access Manager requires that the RDBCNNMTH parameter be set to *DUW for any SQL package that is preprocessed with any of the CRTSQLxxx commands.

Non-DML SQL Statements

When using two-phase commit processing, CONNECT is the only non-Data Manipulation Language (DML) SQL statement that is supported by Access Manager. Use of such non-DML statements results in the following error:

ORA-2089 COMMIT is not allowed in a subordinate session

COMMIT HOLD

The HOLD parameter on an EXEC SQL COMMIT HOLD command is not honored. All cursors are closed at each logical unit of work boundary (COMMIT and ROLLBACK).

LONG and LONG RAW Data Types

Only the first 32,740 bytes in a LONG or LONG RAW column can be retrieved. This is because of the DB2/400 length limit on a LONG VARCHAR column. Also note that the total data length on a SELECTed row is 32,760 bytes. The maximum length for a LONG or LONG RAW column will be further diminished by the amount of data in the OTHER columns that are retrieved in that row.

Installation into an IASP

The Access Manager can not be installed into a library that is part of an independent auxiliary storage pool (IASP). This is an IBM restriction on the placement of exit programs and subsystem descriptions. To get around this, just install the Access Manager in normal libraries on the machines/partitions that will be participating in the IASP. You should modify the user profiles of those users that will be using the Access Manager to retrieve DB2/400 data in an IASP so that the initial program (as defined in the User Profile) sets the current IASP group using the SETASPGRP command.