To use Oracle Access Manager for AS/400, you need to understand the following topics:
When the network definition is completed, you can connect to an Oracle server from interactive SQL or from the precompiled AS/400 program.
From the AS/400 command line, enter:
STRSQL
Ensure that your interactive STRSQL session uses the SQL syntax library.file. To ensure this, use STRSQL NAMING(*SQL). Access Manager does not support the AS/400 library/file syntax.
From the interactive SQL command line, enter the IBM DB2/400 CONNECT TO command in order to connect to the Oracle server:
CONNECT TO remote_oracle USER userid USING 'pw'
where:
remote_oracle is your RDB directory entry that has a matching TNSNAMES entry.
userid is a valid Oracle user ID.
pw is the password for the Oracle user ID..
If no user ID is specified, then Access Manager uses the default user ID and password. The default user ID and password are SCOTT and TIGER. You can change these with the CHGSQLDFLT command. Refer to Chapter 6, "Configuring Access Manager" for more information.
After you are connected, you can issue SQL statements to the remote Oracle server.
Three sample programs are distributed with Access Manager. They are SAMP1C, SAMP1CBL, and SAMP1RPG. Use the CRTORAPKG command to precompile, compile, and bind these programs. Refer to Chapter 6, "Configuring Access Manager" for more information about the CRTORAPKG command.
After using the CRTORAPKG command, you can execute the sample programs using the CALL command from the AS/400 command line. For example:
CALL SAMP1RPG
Each program explicitly connects to a server called GENERIC using an Oracle server user name of SCOTT and a password of TIGER. You can edit these sample programs if you want to test them against a different server or if you want to use a different user name or password.
After execution, you can issue a SELECT statement for the DEPT table through interactive SQL (STRSQL) to verify that the rows were inserted successfully.
These sample programs (included in Appendix A, "Sample Programs") are in the Access Manager installation library in the following files:
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.
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.
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.
As defined on the AS/400, a stored procedure is a programming construct that can be called from within the SQL environment to perform a set of operations. The operations can include host language statements, SQL statements, and logic. When connected to an Oracle server, the host language statements are packaged within a PL/SQL stored procedure on the Oracle server.
Stored procedures in PL/SQL provide the same benefits as procedures in a host language such as C or COBOL. A PL/SQL procedure that is stored in the Oracle Database can be called from several programs. The use of stored procedures can also enhance the performance of a distributed application. For example, assume that you want to execute several SQL statements at an Oracle server using interactive SQL. If stored procedures are not used, then the AS/400 must send a separate request to the Oracle server for each SQL statement. If the same SQL statements are stored in a stored procedure at the Oracle server, then a single CALL statement on the AS/400 executes the entire block of SQL statements.
Parameters that are used only to pass data to a stored procedure are called IN parameters. Constant values that are used as parameters can be used only as IN parameters. Parameters that are used only to return data from a stored procedure are called OUT parameters. Parameters that can be used in both directions are called IN OUT parameters. You must use a host variable to have data returned from an Oracle stored procedure.
Calls to a stored procedure on the Oracle server from within an AS/400 application can use IN, OUT and IN OUT parameters. This enables parameters to be passed to the stored procedure and data to be returned. Calls to a stored procedure from within the interactive SQL environment can use only IN parameters. When using interactive SQL (STRSQL), you can pass constant parameters to the stored procedure on the Oracle server, but no data can be returned.
When calling an Oracle stored procedure, you must use the DB2/400 DECLARE PROCEDURE statement. This statement is used to specify the IN, OUT, or IN OUT capability and the data type of each parameter. For example:
DECLARE Sample PROCEDURE (IN :arg1 INTEGER, IN OUT :arg2 CHAR(20),
OUT :arg3 SMALLINT) (LANGUAGE C SIMPLE CALL)
Caution:
If aDECLARE PROCEDURE statement is not found for a CALL to a stored procedure, then each parameter defaults to IN OUT, and the data type for each parameter defaults to the data type of the associated host variable.Ensure that the data types that are specified in the DECLARE PROCEDURE statement are compatible with those that are found in the stored procedure specification of the Oracle stored procedure. In addition, the input or output modes of each parameter (specified by IN, OUT, or IN OUT in the DECLARE PROCEDURE statement) and the number of parameters must match those of the Oracle stored procedure.
The following example is an extract from an AS/400 C program that calls the Oracle stored procedure GETRESULTS. The data types of parameters match, the input and output modes match, and the number of parameters match (nine parameters are in the DECLARE PROCEDURE statement on the AS/400, in the CALL to the procedure, and in the declaration of the Oracle stored procedure).
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;
/
~
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.
Table 8-2 Oracle data types mapped to DB2/400 data types
| Oracle Server Data Type | DB2/400 Data Type |
|---|---|
|
|
|
|
|
|
|
|
|
|
or
|
|
|
|
|
|
|
|
|
|
|
|
|
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.
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:
| Oracle Error Code | DB2/400 SQLSTATE | DB2/400 SQLCODE |
|---|---|---|
|
ORA-00901 invalid create command |
42601 |
-104 |
|
ORA-00904 invalid column name |
42703 |
-206 |
|
ORA-00911 invalid character |
42601 |
-7 |
|
ORA-00923 from keyword not found |
42601 |
-104 |
|
ORA-00932 inconsistent datatypes |
42884 |
-440 |
|
ORA-00933 SQL command not properly ended |
42601 |
-104 |
|
ORA-00936 missing expression |
42601 |
-104 |
|
ORA-00942 table or view does not exist |
42704 |
-204 |
|
ORA-01095 DML statement process 0 rows |
02000 |
+100 |
|
ORA-01400 cannot insert NULL into string |
23502 |
-407 |
|
ORA-01401 inserted value too large for column |
22001 |
-404 |
|
ORA-01403 no data found |
02000 |
+100 |
|
ORA-01405 fetched column value is NULL |
22002 |
-305 |
|
ORA-01406 fetched column value was truncated |
22001 |
-404 |
|
ORA-01422 exact fetch returns more than requested number of rows |
21000 |
-811 |
|
ORA-01424 missing or illegal character following the escape character |
22025 |
-130 |
|
ORA-01425 escape character must be character string of length 1 |
22019 |
-130 |
|
ORA-01427 single row subquery returns more than one row |
21000 |
-811 |
|
ORA-01438 value larger than specified precision for this column |
22001 |
-404 |
|
ORA-01455 converting column overflows integer datatype |
22003 |
-304 |
|
ORA-01457 converting column overflows decimal datatype |
22003 |
-304 |
|
ORA-01476 divisor is equal to zero |
22012 |
-802 |
|
ORA-01479 last character in buffer is not NULL |
22024 |
-302 |
|
ORA-01480 trailing null missing from STR bind variable |
22024 |
-302 |
|
ORA-01488 invalid nibble or byte in input data |
22023 |
-302 |
|
ORA-12154 TNS: could not resolve service name |
08003 |
-30000 |
|
ORA-12533 TNS: illegal ADDRESS parameters |
08003 |
-30000 |
|
ORA-12541 TNS: no listener |
08003 |
-30000 |
|
ORA-12154 TNS: could not resolve service name |
08003 |
-30000 |
|
ORA-12560 TNS: protocol adapter error |
08003 |
-30000 |
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.