8 Using Access Manager

8.1 Connecting to the Oracle Server

When the network definition is completed, you can connect to an Oracle server from interactive SQL or from the precompiled AS/400 program.

STRSQL

CONNECT TO remote_oracle USER userid USING 'pw'

ORA-00901: invalid CREATE command

CALL SAMP1RPG

8.2 Connection Management

Connection management consists of connecting to and disconnecting from one or more servers, and using COMMIT and ROLLBACK to coordinate transactions.

Access Manager connects to an Oracle server using the DB2/400 CONNECT syntax. The Oracle CONNECT syntax is not supported by Access Manager.

An application can connect to an Oracle server explicitly or implicitly. Explicit connection occurs when the DB2/400 CONNECT TO statement is issued. Implicit connection occurs when the first SQL statement in an application program is not a CONNECT TO statement. If this occurs, then the connection is made to the server that is specified on the RDB parameter in the IBM CRTSQLxxx command.

After a connection is established, the server becomes active, and all SQL statements are processed on that server.

Access Manager imposes no restrictions on the number of connections that can be opened at one time. In a single session, you can connect to multiple Oracle servers and multiple IBM DRDA application servers. When multiple servers are connected, you can switch between servers using the DB2/400 SET CONNECTION command.

For example:

CONNECT TO LOCAL
INSERT INTO DEPT VALUES (50, 'LOCAL', 'ROW 1')
CONNECT TO ORACLE1 USER SCOTT USING 'TIGER' 
INSERT INTO DEPT VALUES (50, 'ORACLE1', 'ROW 1')
CONNECT TO ORACLE2 USER JOHN USING 'SMITH' 
INSERT INTO DEPT VALUES (50, 'ORACLE2', 'ROW 1')
SET CONNECTION ORACLE1 
INSERT INTO DEPT VALUES (50, 'ORACLE1', 'ROW 2')
SET CONNECTION LOCAL 
INSERT INTO DEPT VALUES (50, 'LOCAL', 'ROW 2')

If multiple connections are open and a COMMIT or ROLLBACK statement is issued, then all database servers are requested to COMMIT or to ROLLBACK.

You cannot have multiple connections open to the same RDB name within a single session. For example, the following would be invalid:

CONNECT TO ORACLE1 USER X USING 'PWDX'
CONNECT TO ORACLE1 USER Y USING 'PWDY'

All applications, including interactive SQL (STRSQL), keep all connections open until you do one of the following:

• sign off from your AS/400 system

• issue a RELEASE command for each open connection, followed by a COMMIT statement

• issue a RELEASE ALL command, followed by a COMMIT statement, thereby ending all connections

Exiting Interactive SQL (STRSQL) does not affect the status of any connections that were obtained (and are still active) within Interactive SQL. All Oracle server connections that are left open are available on subsequent invocations of Interactive SQL within the same job. However, exiting a program that uses embedded SQL in the EXEC SQL format will cause any Oracle server connection that was created within that program to be released.

Access Manager connects to an Oracle server using CONNECT type 5 and establishes a protected conversation, as documented in the IBM DB2/400 SQL Programming Guide.

SQL0858 "Cannot disconnect relational database
<server_name> due to LU6.2 protected conversation"

8.3 Describing Tables

Interactive SQL (STRSQL) does not allow the DESCRIBE TABLE to be issued interactively. However, column and data type information can be obtained through interactive SQL (STRSQL) by using the prompt option (PF4) when issuing a SELECT from the command line. This information can be used by application programmers to understand how Oracle data types are mapped to AS/400 data types.

8.4 Stored Procedures

On the AS/400, you call a stored procedure by using the IBM DB2/400 CALL statement. The CALL statement can be executed interactively from interactive SQL or embedded in an application within the appropriate EXEC-SQL END-EXEC delimiters for the host language.

Access Manager allows access from AS/400 applications to Oracle stored procedures or packages by providing mapping from the IBM CALL statement to an Oracle stored procedure.

The results of a CALL command can be committed or rolled back. Access Manager always assumes that an invocation of stored procedures has changed something on the Oracle server.

DECLARE Sample PROCEDURE (IN :arg1 INTEGER, IN OUT :arg2 CHAR(20), 
       OUT :arg3 SMALLINT) (LANGUAGE C SIMPLE CALL) 

EXEC SQL BEGIN DECLARE SECTION; 
char p1[10], p3[10], p5[10]; 
struct {short len; char value[20];} p1a;  /* A VARCHAR */ 
struct {short len; char value[20];} p3a;  /* A VARCHAR */ 
struct {short len; char value[20];} p5a;  /* A VARCHAR */ 
long p2, p4, p6; 
EXEC SQL END DECLARE SECTION; 
. . . . . 
EXEC SQL DECLARE GETRESULTS PROCEDURE 
(IN :par1 CHAR(10), IN :par1a VARCHAR(20), IN :par2 INTEGER, 
 INOUT :par3 CHAR(10), INOUT :par3a VARCHAR(20), 
 INOUT :par4 INTEGER, 
 OUT :par5 CHAR(10), OUT :par5a VARCHAR(20), 
 OUT :par6 INTEGER) 
( LANGUAGE C SIMPLE CALL); 
EXEC SQL CALL GETRESULTS(:p1, :p1a, :p2, :p3, :p3a, :p4, :p5, :p5a,
                         :p6);
. . . . . 
The following is the file "getresults.sql" on the ORACLE server. 
create or replace procedure GETRESULTS 
(A1   IN     char := 'DefA1', A1A IN     VARCHAR2, A2 IN     NUMBER, 
 A3   IN OUT char, A3A IN OUT VARCHAR2, A4 IN OUT NUMBER, 
 A5   OUT    char, A5A OUT    VARCHAR2, A6 OUT    NUMBER) 
IS 
BEGIN 
 A5 := A3; A5A := A3A; A6 := A4; 
 A3 := A1; A3A := A1A; A4 := A2; 
END; 
/ 
~ 

8.5 Conversion of Oracle Data Types to DB2/400 Data Types

Oracle Access Manager for AS/400 converts Oracle data types to DB2/400 data types to allow application host variables to store and represent the Oracle data that they receive into a format that is native to the AS/400 environment.

The following table describes these data type conversions. In the table, p refers to precision, and s refers to scale. On DB2/400, VARCHAR can contain 32 740 characters at most.

LONG data types or combinations of LONG and LONG RAW data types are limited by the DB2/400 restriction that the entire length of data returned for a row must be less than 32 767 bytes. This restricts LONG and LONG RAW data types to a maximum of 32 740 bytes for all the LONG and LONG RAW data types that are returned in a row.

8.6 Message and Error Code Processing

Access Manager communicates all error conditions to the AS/400 application program through the SQLCA. Because Oracle error codes differ from DB2/400 error codes, a one-to-one mapping is not possible for all error conditions. Therefore, Access Manager uses the SQLSTATE field of the SQLCA as a mechanism to communicate Oracle errors to DB2/400.

When a SQLSTATE other than 00000 is returned from Access Manager, DB2/400 attempts to map the SQLSTATE to a DB2/400 SQLCODE and message. If it is unable to recognize the SQLSTATE, then DB2/400 issues the common message for that class code. For a list of SQLSTATE class codes, refer to the IBM DB2/400 SQL programming guide.

In some situations, Access Manager translates a SQLSTATE value into a valid DB2/400 SQLCODE. This is called a mapped error code. When mapping occurs, a valid DB2/400 SQLCODE is returned, and all applicable substitution tokens are placed in the SQLERRMC field of the SQLCA. This enables you to see valid text in the replacement variables of the DB2/400 SQL error message. Access Manager currently maps these error codes as indicated in the following table:

If DB2/400 does not recognize a SQLSTATE, then the following message is issued:

SQL7940 SQLSTATE class unknown, SQLSTATE xxxxx...

Examine the job log for the relevant Oracle error that caused the SQLSTATE xxxxx to be issued.

If Access Manager is unable to map an error code, then it returns a SQLCODE of -969 and a SQLSTATE which applies to the class code of the error. For example, all syntax errors have a SQLSTATE class code of 42. Therefore, if Access Manager receives a syntax parsing error from the Oracle server (which it cannot map), then it returns SQLCODE of -969 and SQLSTATE 42000.

If you are unable to determine the root cause of the error based on the SQLCODE and SQLSTATE that are returned to the application, then check the job log for more details. All Oracle errors and message text are displayed in the job log at the time that the error occurs.