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 CRTSQL
xxx
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.