Home

Oracle Database Gateway for DB2 Installation and User's Guide

1. NUM FREELIST BLOCKS N A DEGREE N A INSTANCES N A CACHE N A TABLE LOCK N A SAMPLE SIZE N A LAST ANALYZED N A PARTITIONED N A IOT TYPE N A TEMPORARY N A SECONDARY N A NESTED N A BUFFER POOL N A ROW MOVEMENT N A GLOBAL STATS N A USER STATS N A DURATION N A SKIP CORRUPT N A MONITORING N A C 8 Oracle Database Gateway for DB2 400 Installation and Users Guide Column Name Description CLUSTER OWNER N A DEPENDENCIES N A COMPRESSION N A DROPPED N A C 12 ALL USERS Information about all users of the database Column Name Description US ERNAME USER_ID CR EATED Name of the user N A N A C 13 ALL VIEWS Text of views that are accessible to the user Column Name Description OWNER VI EW NAME TEXT LENGTH TEXT TYPE TEXT TYPE TEXT LENGTH OID TEXT LENGTH OID TEXT VI EW TYPE OWNER VI SUPERVIEW NAME EW TYPE Owner of the view Name of the view Length of the view text View text Only the first row of text is returned even if multiple rows exist N A N A N A N A N A N A N A C 14 COLUMN PRIVILEGES Grants on columns for which the user is the grantor grantee or owner or PUBLIC is the grantee Column Name
2. With release 10 of the gateway the Oracle Database 10g server supports fetch reblocking with the RPC FETCH REBLOCKING parameter When the value of this parameter is set to YES the default the block size of the buffer for SELECT statements is determined by the RPC FETCH SIZEvalue The RPC FETCH SIZE parameter defines the number of bytes sent with each buffer from the gateway to the Oracle Database 10g server The buffer might contain one or more qualifying rows from DB2 400 This feature can provide significant performance enhancements depending on your application design installation type and workload The RPC FETCH REBLOCKING and RPC FETCH SIZE parameters may be changed by using the CHGORATUN command Refer to CHGORATUN Change Initialization Parameters on page 6 6 for more information The array size between the client and the Oracle Database 10 server is still determined by the Oracle application To ensure optimal performance Oracle Developing Applications 9 3 Corporation recommends that you set your Oracl setting of the gateway ARRAY BLOCK SIZE para the gateway ARRAY BLOCK SIZ on page 6 10 For more information about array p e application array size equal to the meter For more information about E parameter refer to Setting Optional Parameters rocessing usage and implementation in your Oracle application refer to Oracle Data
3. The GENERAL linkage convention means that the parameters that are passed to DB2 400 stored functions cannot be NULL The GENERAL WITH NULLS linkage convention means that parameters passed to DB2 400 stored functions can be NULL when they are passed using indicator variables Oracle Call Interface OCI or embedded PL SQL can be used in host programs to operate on indicator variables See the appropriate DB2 400 IBM references on how to detect NULLs passed as arguments to a DB2 400 user defined functions 9 7 Passing DB2 400 SQL Statements Through the Gateway The passthrough SQL feature allows an application developer to send a SQL statement directly to DB2 400 without the statement being interpreted by the Oracle database DB2 400 SQL statements that are supported by the gateway through the passthrough facility are limited to non queries INSERT UPDATE DELETE and DDL statements and cannot contain bind variables The gateway can run native DB2 400 SOL statements using the DBMS_HS_PASSTHROUGH EXECUTE_IMMEDIATE function DBMS HS PASSTHROUGH EXECUTE IMMEDIATE is a function that is built into the gateway This function takes one input argument and returns the number of rows that are affected by the SQL statement For DDL statements the function returns zero DBMS H8 PASSTHROUGH EXECUTE IMMEDIATE is a reserved name of the gateway and is used specifically for runn
4. 9 7 2 Examples Following are examples of the DBMS HS PASSTHROUGH EXECUTE IMMEDIATE function Refer to the next section Using Passthrough Statements to Create Tables for more information 1 Inserta row into a DB2 400 table using DBMS HS PASSTHROUGH EXECUTE IMMEDIATE declare num rows NUMBER begin num rows DBMS HS PASSTHROUGH EXECUTE IMMEDIATEGdblink INSERT INTO SCOTT DEPT VALUES 10 PURCHASING PHOENIX end 2 Create a DB2 400 table using DBMS HS PASSTHROUGH EXECUTE IMMEDIATE declare num rows NUMBER begin num rows DBMS HS PASSTHROUGH EXECUTE IMMEDIATEGdblink CREATE TABLE MYTABLE COL1 INTEGER COL2 INTEGER COL3 CHAR 14 COL4 VARCHAR 13 end 9 7 3 Using Passthrough Statements to Create Tables If you use DBMS HS PASSTHROUGH EXECUTE IMMEDIATE to create a DB2 400 table then one of the following is required for journaling a QSQJRN journal in the target library a target library that was created as a collection This requirement applies if the DB2 400 isolation level is set to CHG which is the default If neither of the above is true then you will receive an error message that the table has been created but cannot be journaled In this case you can disregard the error message and journal the file manually For more information on journaling r
5. 5 4 1 Configuring for TCP IP Perform the following steps to configure your TCP IP connection between the Oracle Database 10g server and the AS 400 Step 1 Configure the Port Number Step 2 Define the Host Name Step 3 Verify the Host Name Step 4 Verify that the Listener is Started 5 4 2 Step 1 Configure the Port Number If the gateway is already assigned to port 1521 or to another available port number then go to Step 2 Define the Host Name 5 6 Oracle Database Gateway for DB2 400 Installation and User s Guide Configuring Oracle Net for TCP IP AS 400 The gateway listener must be assigned a dedicated port number on which to listen for incoming connection requests The default port number is 1521 This number can be changed during installation if you know that port 1521 is already used or if you prefer to use a different port number for any other reason The port number may also be changed after installation by using the CHGORANET command You can find the values of port numbers that are currently active by using the AS 400 command NETSTAT CNN Each running instance requires a unique listener port number Two gateway instances that are active at the same time cannot use the same listener port number For more information refer to CHGORANET Change Network Parameters on page 6 5 5 4 3 Step 2 Define the Host Name To define the host name perform the following steps 1 Runthe AS 400 CFGTCP command 2 Select op
6. 0 0 255 Listener pause during job start NORMAI NORMAL PAUSE Listener show GETENV messages NORMAI NORMAL YES Listener continue after error NORMAI NORMAL CONTINUE Listener hang on error NORMAI NORMAL HANG Listener hang time in minutes DAY NORMAL 1 10080 HOUR DAY WEEK gt Bottom Fl Help F4 Prompt F9 Retrieve F12 Cancel After entering the new values press Enter to continue The new values do not take effect until you shut down and restart the gateway specified in the Existing instance name parameter of the command 6 1 7 CRTORADDB Create Data Dictionary Views This command submits a batch job to create Oracle data dictionary views of the system catalog If you are going to run an application such as Oracle Developer then you need to create data dictionary views Therefore if you create these views when originally installing the gateway then you should not need to create them again On the OS 400 command line use the CRTORADDB command or use the GO CMDORAGTW command and specify option 6 Then press Enter Enter the appropriate instance name and press Enter A batch job is submitted No additional panel appears The Oracle Data Dictionary views of the system catalog are used by all Oracle Gateway instances on that AS 400 One copy of these views is on each AS 400 6 1 8 CHGRECOPRF Change Recovery Profile Parameters This command changes the OS 400 User Profi
7. Lower numbers represent higher priority levels Installing the Gateway 4 9 4 10 Oracle Database Gateway for DB2 400 Installation and User s Guide 5 Oracle Net Oracle Net is an Oracle product providing distributed database and processing capabilities Generally Oracle Net for AS 400 supports network communications between Oracle applications Oracle servers and Oracle gateways across different AS 400 systems or foreign operating systems For product specific information refer to the Oracle Database Net Services Administrator s Guide and the Oracle Database Net Services Reference This chapter presents information about the Oracle Net architecture and how to configure and use Oracle Net for AS 400 It contains the following sections Overview of Oracle Net on page 5 1 Connecting to the AS 400 through TCP IP on page 5 4 Checklists for Configuring Oracle Net on page 5 5 Configuring Oracle Net for TCP IP AS 400 on page 5 6 TCP IP Connection Problems on page 5 8 Resolving Connection Problems on page 5 8 Troubleshooting TCP IP Configuration on page 5 10 5 1 Overview of Oracle Net Oracle Net is a required Oracle product supporting network communications between Oracle applications Oracle servers and Oracle gateways across different CPUs and operating systems It also supports communication across different Oracle Databases and CPUs providing distributed database and distributed processing
8. Oracle Database After the listener accepts the client connection a batch job is started on the AS 400 or more usually a prestarted AS 400 batch job receives control The batch job executes the gateway and sends a response back to the client The response is data or a message Each batch job is responsible for a client connection Only one batch job is 5 2 Oracle Database Gateway for DB2 400 Installation and Users Guide Overview of Oracle Net executed for a single connection and a single connection is associated with only one batch job To reduce the waiting time for connecting to gateway jobs a set of prestarted gateway batch jobs are associated with a TNS listener The number of prestarted jobs and the point at which additional jobs are started can be modified by the system administrator 5 1 5 File Name Structure Oracle Net refers to files in the following format basename extension where basename is the base portion of the name and extension is the second part of the name An example of this form is SOLNET ORA On the AS 400 the Oracle Net parameter files are installed in the gateway instance library using the following file name mapping a The base portion of the file name is mapped to a member name in an AS 400 file The extension portion of the file name is mapped to an AS 400 file name For example the SOLNET ORA parameter file is mapped to member SOLNET in the ORA file that is
9. a Ifthe BLOCKSIZ E data area does not exist then the gateway will retrieve one row per fetch from DB2 400 6 10 Oracle Database Gateway for DB2 400 Installation and User s Guide Setting Optional Parameters Note For performance reasons Oracle recommends that the BLOCKSIZE data area be set between 10 and 100 The gateway uses a default value of 100 for the BLOCKSIZE data area The BLOCKSIZE value is used for substitution for host variable N in a SOL FETCH statement such as FETCH CN for N ROWS To change this default value use the gateway command CHGORATUN After displaying the panel for CHGORATUN enter a new value for Array block size Refer to CHGORATUN Change Initialization Parameters on page 6 6 for information about changing the setting for Array block size 6 2 2 Data Conversion The default coded character set identifier system value QCCSID for the AS 400 system is 65535 This CCSID value indicates to the gateway that character data in a column with such a CCSID is not to be converted and is to be treated as bit data The line entitled Option for CCSID 65535 fields on the Change Oracle Gateways parameters panel use the CHGORATUN command specifies how the gateway is to handle the For Bit Data and CCSID 65535 fields If the specification is BITDATA then the fields are treated as binary data and no translation occurs If CHARDATA is specified then the fields
10. 2 4 2 4 1 2 4 1 1 2 4 1 2 2 4 1 3 2 4 1 4 2 4 1 5 2 4 1 6 2 4 1 7 2 4 1 8 2 4 1 9 2 4 2 2 4 2 1 2 4 2 2 2 4 2 3 2 4 2 4 2 4 2 5 2 4 2 6 2 4 2 7 2 4 3 2 4 3 1 2 4 3 2 2 4 3 3 2 4 3 4 Product S6U on de e ee Aan aes 2 1 Changes arid Enharicemierits oett eet e dee Ire ere tpi e tree eee 2 1 USS wisn ies s ote c tmu peg ted notado me c bs bt Series 2 1 Known Problems te iene t heel eee e atte ena icc ede Pee a ine 2 2 Known RestricHons iiia Ree ir de e rii ee e lis ERES eee 2 3 General Resttictotis ERE eret thee tertie t te e rt Se ee eles 2 3 SQL Development Kit Product 5722 ST1 sse ee eene 2 3 Selecting CHAR Column Returns Width Doubled sss 2 3 Coexistence with Previous Version Gateway Data Dictionary Tables 2 3 Oracle Fast Refresh Snapshots sse 2 3 Oracle SOL Command INSERT esses eerte nennen nnne 2 3 Verify the OCRTAUT Parameter Setting Before Installation 2 3 Date Arithmetic u n eere erecti tee rcc to ete e eee Hee ee CER de 2 3 Julian Dates Support ete eee tene be eere rites 2 4 GRAPHIC Constants in SOL Commands eese nennen 2 4 Owners of DB2 400 Components sse e enne e n nnne nennen 2 4 DD Basic Tables and VieWS cccccssesscessesscesceeseesceseessceseecsecssesaecesssaeceeseseseeseseeeeeeaeeess 2 4 Binary Literal Notation ecc eet mete ete tt pe
11. A number of Globalization Support parameters control Globalization Support processing between the Oracle database and the client You can set language dependent behavior defaults for the server and set language dependent behavior for the client to override these defaults For a complete description of Globalization Support parameters refer to the Globalization Support chapter in the Oracle Database Globalization Support Guide These parameters do not affect gateway processing However you must ensure that the character set is compatible with the character set that you specify on the gateway and DB2 400 In other words each character in one encoding scheme must have a matching character in another encoding scheme Caution Character sets must be compatible for successful data transfer Make sure that you know which character sets are being used and that they are compatible When you create your Oracle Database the character set that is used to store data is specified by the CHARACTER SET parameter After the database is created the database character set cannot be changed unless you re create the database The Oracle database default value for CHARACTER SET is platform dependent and version dependent The value needs to be compatible with the CHARACTER SET that is used by the gateway To check the character set of an existing database issue the following command in SQL Plus SELECT userenv language FROM DUAL B
12. Description GRANTEE OWNER TABLE NAMF Name of the user to whom access is granted User name of the owner of the object Name of the object Data Dictionary Views C 9 DICTIONARY Column Name Description COLUMN NAME Name of the column GRANTOR Name of the user who performed the grant INSERT PRIV Permission to insert into the column UPDATE PRIV Permission to update the column REFERENCES PRIV Permission to reference the column CREATED Time stamp for the grant C 15 DICTIONARY Information about the tables in the Data Dictionary Column Name Description TABLE NAME Name of the table in the dictionary COMMENTS Comments about this dictionary entry C 16 DUAL Information about the all purpose single row DUAL table Column Name Description DUMMY A dummy column has value X C 17 OTGREGISTER DB2 400 Special Registers Column Name Description CURRENT USER Primary authorization id CURRENT DATE Current date CURRENT TIME Current time CURRENT TIMESTAMP Current time stamp CURRENT TIMEZONFE Current time zone CURRENT SERVER Host name of the current server current user Primary authorization id current date Current date current time Current time current timestamp Current time stamp current timezone Current time zone current server Host name of the cu
13. 5 3 triggers Oracle Database server 7 7 1 7 8 synchronous copies of Oracle and non Oracle data maintained automatically 7 8 twenty first century dates 9 19 two phase commit description 7 7 in multi site transactions 1 6 prepare phase not supported by DB2 400 7 7 recorded in 7 7 transaction recovery 7 7 transaction treated as a single unit of work 1 3 updates to DB2 400 made within Oracle stored procedure 9 5 U UNION capability distributed capabilities 1 3 user class OSECOFR 4 2 USER 4 3 user ID cannot be longer than ten characters 7 2 CONNECT TO user ID provides implicit qualification for unqualified tables 7 2 gateway recovery caution 6 10 instance password for gateway copy 6 4 instance password name choices on install panel 4 4 Oracle user ID passed over database link for gateway security 8 4 Oracle user ID used as implicit qualifier 7 2 OS 400 user ID identical to name of install library 4 2 OS 400 user ID known as gateway ID 4 2 recovery user ID recovering failed transactions 7 8 remote user ID specified by CONNECT TO clause creating databaselinks 7 1 starting the Oracle gateway monitor 8 1 that is used to establish a session in remote AS 400 system 7 2 user ID CONNECT TO 7 2 Exe CU Cc ccce coercet SER CATALOG view description C 11 SER COL COMMENTS view description C 11 SER CONS COLUMNS view description C 11 SER CONSTRAINTS view description C 12
14. DB2 CREATE TABLE 1 5 DROP DATABASE LINK 7 3 INSERT example for double byte character support 9 16 SELECT fetch reblocking 9 3 retrieve data from multiple databases 9 2 without the FOR UPDATE clause 9 22 SOL gateway appearance to application programs 9 1 passing through gateway 9 12 stopping the gateway 8 4 stored procedures columns ROUTINE NAME 9 6 ROUTINE SCHEMA 9 6 considerations 9 7 database triggers extended database services 1 3 DB2 400 with the gateway 9 5 execution of DB2 400 procedures from Index 11 applications 9 6 gateway capabilities intro 1 5 gateway support of Oracle stored procedures 9 4 linkage conventions 9 7 native DB2 1 5 Oracle stored procedures introduction 1 5 PL SQL 9 5 synonyms for 9 4 two phase commit 9 5 using 9 4 streams replication 8 5 example code 8 5 replication from an Oracle Database to DB2 400 is supported 8 5 streams will not work if archivelog is not enabled 8 5 STRORALSN command 5 8 STRSBS command 1 7 Structured Query Language See SOL 1 2 SUBSTR function always post processed by Oracle server 1 9 SUBSTR function not same in Oracle server and other database servers 1 9 support contacting Oracle Support Services 10 3 synonym feature 7 3 syntax conventions used in this book 0 xii I TABLE PRIVILEGES view description C 11 TCP IP AS 400 database 5 2 bequeath mechanism 5 6 concurrent connections memory 3 1 configuring Oracle Net
15. For indexes enforcing UNIQUE and PRIMARY KEY constraints this value is the same as the number of rows in the table N A N A N A State of the indexes VALID N A N A N A N A N A N A Data Dictionary Views C 13 USER OBJECTS Column Name Description TEMPORARY N A GENERATED N A SECONDARY N A BUFFER POOL N A USER STATS N A DURATION N A PCT DIRECT ACCESS N A ITYP OWNER N A ITYP NAME N A PARAMETERS N A GLOBAL STATS N A DOMIDX STATUS N A DOMIDX OPSTATUS N A FUNCIDX STATUS N A JOIN INDEX N A IOT REDUNDANT PKEY N A DROPPED N A C 24 USER OBJECTS Objects that are owned by the user Column Name Description OBJECT NAME SUBOBJECT NAME OBJECT ID DATA OBJECT ID OBJECT TYPE CREATED LAST DDL TIME TIMESTAMP STATUS mM Ei MPORARY GENERATED nN ECONDARY C 25 USER_SYNONYMS Name of object N A Object number of the object N A Type of object N A N A N A State of the object VALID N A N A N A The user s private synonyms C 14 Oracle Database Gateway for DB2 400 Installation and User s Guide Column Name Description SYNONYM NAMI El TABLE_OWNER TABLE_NAME DB_LINK Name of the synonym Owner of the object referenced by the synonym Name of the object referenced by the sy
16. SER DB LINKS data dictionary view examining available databaselinks 7 3 SER INDEXES view description C 12 SER OBJECTS view description C 14 SER SYNONYMS view description C 14 SER TAB COLUMNS view description C 15 SER TAB COMMENTS view description C 16 SER TABLES view description C 16 SER USERS view description C 17 SER VIEWS view description C 18 SING clause specifies a TNSNAMES ORA connect descriptor 7 2 V VALUE translated SOL function 9 21 VARCHAR DB2 400 VARCHAR data typelength 9 18 general restrictions column returns its width doubled 2 3 table data type mapping and restrictions 9 14 VARCHAR LONG long data types 2 6 VARCHAR data types DB2 400 GRAPHIC support 9 16 views data dictionary ALL CATALOG description C 2 ALL COL COMMENTS description C 2 ALL CONS COLUMNS description C 2 ALL CONSTRAINTS description C 3 ALL DB LINKS examining available database links 7 3 ALL IND COLUMNS description C 3 ALL INDEXES description C 4 ALL OBJECTS description C 5 ALL SYNONYMS description C 6 ALL TAB COLUMNS description C 6 ALL TAB COMMENTS description C 7 ALL TABLES description C 7 ALL USERS description C 9 ALL VIEWS description C 9 COLUMN PRIVILEGES description C 9 DBA DB LINKS examining available database links 7 3 DICTIONARY description C 10 DUAL description C 10 OTGREGISTER DB2 400 special registers 9 23 OTGREGISTER d
17. type is converted to an Oracle VARCHAR2 data type if it is between 1 and 4000 characters in length If it is between 4001 and 32740 characters in length then it is converted to an Oracle LONG data type The Oracle LONG data type can be from 1 byte to 2 GB in length Because the DB2 400 VARCHAR data type can be no longer than 32740 bytes you receive an error message if you attempt to insert data longer than 32740 bytes into a DB2 400 VARCHAR or LONG VARCHAR data type 9 8 5 Performing Date and Time Operations The implementation of date and time data differs significantly in DB2 400 and the Oracle database The Oracle database has a single date data type DATE that can contain both calendar date and time of day information DB2 400 supports the following date and time data types DATE isthe calendar date only TIME isthe time of day only TIMESTAMP isanumerical value that combines a calendar date and time of day with microsecond resolution 9 18 Oracle Database Gateway for DB2 400 Installation and User s Guide Converting DB2 400 Data Types to Oracle Data Types There is no mechanism that translates the IBM TIME and TIMESTAMP data to Oracle DATE data An application must process TIME data types in the Oracle CHAR format with a length of 8 bytes An application must process the TTMESTAMP data type in the Oracle CHAR format with a length of 26 bytes An application reads TIME and TIME
18. 2 1 2 1 2 Extended Database Services sse eese eene nennen nennen 1 3 1 2 1 3 Extended Advanced Networking Internet and Intranet Support 1 4 1 2 1 4 Dynamic Dictionary Mapping eee ee een en n nenne 1 4 1 2 2 eR ese Se et ces 1 4 1 2 2 1 Passthrough and Native DB2 400 SQL sssseseseeeeee eene 1 5 1 2 3 Stored Procedures and Functions essent ener enn 1 5 1 2 3 1 Oracle Stored Procedures and Functions eese 1 5 1 2 3 2 Native DB2 400 Stored Procedures essere nnne enne 1 5 1 2 3 3 Native DB2 400 Stored Functions User Defined Functions 1 5 1 2 4 Languages 5 osse A ua aveteaaassesvesansafelstaysa0scsetsoaeshaas E E ER EHE ERR reg 1 5 1 2 5 SOELZPI S A emite im e 1 6 1 2 6 Oracle Database 10g Server Technology and Tools esses 1 6 1 3 Two Phase Commit and Multisite Transactions sse 1 6 1 4 Site Nro 1 6 1 5 Migration and Coexistence e oe eem pc e ipei tee rot Pt foedera tee trea 1 6 1 6 solui HT 1 6 1 7 Gateway Architecture dit dit ee estt eset ees 1 7 1 8 How the Gateway Works eec ete t pre cedi dte e erret 1 7 1 8 1 Gateway Changes to the AS 400 System sse eee nennen 1 8 1 8 2 SOL Differences iii 1 8 1 8 3 Heterogeneous Services Architecture sss see eene 1 9 Release Information 2 1 2 2 2 2 1 2 3
19. 2 Using DB2 400 C ursors x ci ee eere percer iore nde Pete i DER de ih rai 7 3 7 3 7 4 7 4 1 7 5 7 5 1 7 6 7 6 1 7 6 1 1 7 6 1 2 7 6 2 7 6 2 1 7 7 7 7 1 7 7 2 7 8 7 8 1 7 8 2 7 9 Using the Synonym Feattr es penei eene ener nen nennen nennen ne narra nano 7 8 Accessing tlie Gateway srest reineta eine aa nettes 7 4 Viewing A ite S dette edt re ee ee E Re 7 4 Accessing O5 400 File Members rsisi kitari irava titi 7 5 Accessing Flat Eles citet ettet detti ta air 7 5 Performing Distributed Queries sse eene nenne nennen 7 6 Example of a Distributed Query nene n nnns 7 6 SOL Command Example Ipazia eree e ea ee nnne 7 6 SOL Command Example 2 che ii ia 7 6 Two Phase Commit Processitig 2 ettet inte eee erdt dte idas 7 7 Recovering Failed Transactions sse nennen nnns 7 7 Replicating in a Heterogeneous Environment sss eee nene 7 8 Oracle Database Server Triggers sss eee eene nennen 7 8 Oracle Materialized View i ius RI ett t e Rie dt ee ee Ee See 7 8 Copying Data from Oracle Server to DB2 400 Server sss 7 8 A e ioo AO 7 8 SOL Plus COPY Command dee eee eet ta een 7 9 Copying Data to Oracle Server from DB2 400 Server sss 7 9 Administering the Gateway 8 1 8 2 8 3 8 3 1 8 3 2 8 4 8 5 8 6 8 7 8 8 8 9 Overview of the Oracle Gateway Monitor sess eee nens 8 1 Starting the Oracle Gateway Monitor sss 8 1 St
20. 4 2 2 Installation Checklist eee ete eee ee ets 4 2 4 2 3 Postinstallation Checklist 2 eee ie 4 2 4 3 Preinstallation Steps oreet te feat acts 4 2 4 3 1 Step Te TO BOM HE 4 2 4 3 2 Step 2 Verify System Value for the OCRTAUT Parameter sees 4 2 4 4 Installation S16p8 255 NL eee ante saz ee e t e o eem 4 2 4 4 1 Step 1 Mount the CD ROM iiie citet e e e ern airis 4 3 4 4 2 Step 2 Start the Installation Process sess eee eene eene 4 3 4 4 8 Step 3 Set Required Parameters sse nennen n nennen 4 3 4 4 3 1 Patios vei reste e hose ee edie ER EE en ae es 4 3 4 4 3 2 O P ets ewan Ses Eee eed a a ae 4 3 4 4 4 Step 4 Verify InstallatiOti 5 entire acini ani eo ee ic qe eec HET ret dein 4 5 4 4 4 1 Palulo condice iodo 4 5 4 4 4 2 ACHON IU hee et dea ed Eae fen bi a De tidie uale e 4 5 4 4 5 Step 5 Finish the Installation 4 terere teer ee eee 4 6 4 4 6 Step 6 Start the Gateway Subsystem sssssssssseeeeeeeenenee e enne 4 6 4 5 Postinstall ation Steps nette rere A ele e ees 4 6 4 5 1 Joutrrialing eoe EHE erre E ete eet eee m Eu 4 6 4 5 2 Step 1 Verify the Gateway Version ssssssssseeeeeeeneeeenen nene nnns 4 7 4 5 3 Step 2 Verify Journal Bovina 4 7 4 5 4 Step 3 Journal the ORACLE2PC File sese nennen nnne 4 8 4 5 5 Step 4 Configure Oracle Net dd E e Ee e ebd et EE tede ood 4 8 4 5 6 Step 5 Remove Observability sse nennen nnns 4 8
21. 4 5 7 Step 6 Raise the Gateway Priority Level sess eee 4 9 Oracle Net 5 1 Overview of Oracle Net iia eiut enda eso n e e e es 5 1 5 1 1 Distributed Processing iii cene eem ette redet etie iet 5 1 5 1 2 Distributed Database coe iode Le e edge 5 2 5 1 3 Terminology for Oracle Net netter eher dde 5 2 5 1 4 Oracle Net for AS 400 Architecture ccooooccnnoncnnonccconnnnonanananonenanannnccnnanaronnnc nennen nente tenen ena 5 2 5 1 5 File Name Structite kara Ee nines iin SI en Ret 5 8 5 1 5 1 Trace Files tor Job Members ettet eere Eae e e ERU 5 8 5 2 Connecting to the AS 400 through TCP IP sese eee eee enne 5 4 5 2 1 Step 1 Add a TCP IP Connect Descriptor to tnsnames ora sse 5 4 5 2 2 Step 2 Specify SID Namies un estet tee dete tette e teas 5 4 5 3 Checklists for Configuring Oracle Net sss eene nens 5 5 5 3 1 Checklist for Configuring Oracle Net for TCP IP eese 5 5 5 3 2 Connecting Through TCP IP on the Oracle Server Checklist 5 5 5 3 3 Preliminary Step Define a Physical Line eese eee eee 5 5 5 4 Configuring Oracle Net for TCP IP AS 400 sse eene 5 6 5 4 1 Confisuring for TCP IP zie pete ie d eth e t e eee 5 6 5 4 2 Step 1 Configure the Port Number sse eene nennen 5 6 5 4 8 Step 2 Define the Host Name n nnne n nennen nennen 5 7 5 4 4 Step 3 Verity the Host Name siii te ee ee Dre e
22. ASCII wide character or from the process of forcing single byte characters to the corresponding double byte characters As an example use the previous INSERT statement which is INSERT INTO mytable graphcol tg4db2400 values AxxB If you use the FORCE SB option then the resultant DBCS value in the DB2 400 column may look like the following WAyywB where wA is the DBCS correspondence usually 0x42C1 for the single byte A wB is the DBCS correspondence usually 0x42C2 for the single byte B and yy is the DBCS character corresponding to the ASCII based double width xx character When SELECTing from this column on the client you would get exactly what you INSERTed that is AxxB which is good But what if the DBCS character that is represented in the DB2 400 column by wa had actually been INSERTed into the column through a valid ASCII representation for a wide A With the FORCE SB option you get a single byte A on the client This may not be exactly what you wanted In the end it is you the customer who must decide whether this option is valuable to you You must decide if you can accept the possible problems that can arise The ORADBMBOPT Data Area or Environment variable controls this feature If no ORADBMBOPT Data Area or Environment variable is present then no forcing of single byte character to double byte character will take place when transfe
23. CAPTURE ADM TO strmadmin N DBMS FLASHBACK TO strmadmin N DBMS PROPAGATION ADM TO strmadmin N DBMS STREAMS ADM TO strmadmin 0000300 BEGIN DBMS_RULE_ADM GRANT_SYSTEM_PRIVILEGE privilege gt DBMS_RULE_ADM CREATE_RULE_SET_OBJ grantee gt strmadmin grant_option gt FALSE END BEGIN Administering the Gateway 8 5 DBMS RULE ADM GRANT SYSTEM PRIVILEGE privilege DBMS RULE ADM CREATE RULE OBJ grantee strmadmin grant option FALSE END Then set up the Streams queue and the database link that the apply process will use CONNECT strmadmin strmadminpw EXEC DBMS STREAMS ADM SET UP QUEUE DROP DATABASE LINK strmdblink your domain com CREATE DATABASE LINK strmdblink your domain com CONNECT TO userid IDENTIFIED BY password USING tnsnames entry Next create the capture and apply processes and define the replication rules CONNECT SYS SYS PASSWORD AS SYSDBA ALTER SYSTEM ARCHIVE LOG CURRENT CONNECT strmadmin strmadminpw BEGIN DBMS CAPTURE ADM STOP CAPTURE capture name db2400 capture BEGIN DBMS APPLY ADM STOP APPLY apply name apply 2 db2400 BEGIN DBMS STREAMS ADM ADD SCHEMA RULES Schema name scott Streams type capture streams name gt db2400 capture queue name strmadmin streams queue include dml true include ddl true Set the capture instantiation l
24. Installation Steps The panels below beginning with Example 4 1 Install Oracle Database Gateway Panel are shown as they appear when they are first displayed Any values on the panel are default values that are provided by Oracle Default values are used unless you change them before going on to the next panel The installation procedure defines an OS 400 user ID that is identical to the name of the install library This user ID is known as the gateway ID The installation procedure also defines that ID with a user class of USER which has JOBCTL authority This ID is given authority over the entire library To do that the commands executed during installation are 4 2 Oracle Database Gateway for DB2 400 Installation and Users Guide Installation Steps GRTOBJAUT OBJ QSYS gateway id OBJTYPE LIB USER gateway id AUT ALL GRTOBJAUT OBJ gateway id ALL OBJTYPE ALL USER gateway id AUT ALL where gateway id is both the gateway user ID and the library name The security changes that are in the following six lines of example code may be made to allow the gateway to adopt the security authority of the connect to user ID The GRTOBJAUT should be done only if the referenced user in this case PUBLIC does not already have the indicated authority to the object GRTOBJAUT OBJ QSYS QWTSETP OBJTYPE PGM USER PUBLIC AUT USE GRTOBJAUT OBJ QSYS QSYGETPH OBJTYPE PGM USER PUBLIC AUT USE GRTOBJAUT OBJ QSYS QSYRLS
25. One exception is the CONNECT BY clause 9 10 2 SELECT FOR UPDATE INSERT and DELETE Clauses DB2 400 must process the entire SELECT FOR UPDATE INSERT and DELETE clauses The Oracle server cannot post process these clauses Only SQL that is a common subset of Oracle and DB2 400 SOL can be used with these statements The following rules exist for the use of SELECT FOR UPDATE INSERT and DELETE clauses Only Oracle syntax that is also valid for DB2 400 can be used For DB2 400 SOL syntax refer to the IBM reference for DB2 400 The following Oracle functions are supported with all options AVG a MAX MIN m SUM a TO_DATE The NOWAIT option of the FOR UPDATE clause of the SELECT statement is not supported Although DB2 400 requires a list of column names in the FOR UPDATE clause the unqualified Oracle syntax FOR UPDATE with no column names is accepted The gateway derives the column list from the SELECT result column list 9 11 Oracle Server and DB2 400 Differences Differing functions and operations between the two databases are explained below 9 22 Oracle Database Gateway for DB2 400 Installation and User s Guide Oracle Data Dictionary Emulation in a DB2 400 Server 9 11 1 Oracle Bind Variables Oracle bind variables become DB2 400 parameter markers when used with the gateway Therefore the bind variables are subject to the same r
26. Oracle error messages 10 4 incorrect output 10 3 performance 10 4 examples examples of codes and commands see code examples gateway security example code lines 4 3 EXECUTE command beginning a PL SOL routine 1 5 F fetch reblocking array processing 9 3 file INIT ORA GLOBAL_NAMES initialization parameter 2 5 initialization parameter notes 6 7 TNSNAMES ORA creating a database link 7 2 need SID for TCP IP 5 4 file members accessing 7 5 flat files accessing 7 5 FOR BIT DATA option binary literal notation 2 4 FOR UPDATE clause NOWAIT option 9 22 SELECT without FOR UPDATE clause 9 22 FORCE SECS string to force double byte character support 9 17 functions CONCAT translated SOL function 9 21 DB2 400 VALUE SQL differences 1 9 DBMS HS PASSTHROUGH EXECUTE IMMEDI ATE 9 12 9 13 NVL SQL differences 1 9 SOL column 9 20 compensated 9 21 scalar 9 20 translated 9 21 SOL differences converting Oracle Database server NVL function to VALUE 1 9 SUBSTR always post processed by Oracle server 1 9 Index 5 SUBSTR not same in Oracle server and other database servers 1 9 TO DATE dates in the 21st century 9 19 TRANSLATE translated SOL function 9 21 VALUE translated SOL function 9 21 G gateway access 7 4 advantages maintopic 1 2 migration and coexistence 1 6 multisite transactions 1 6 security 1 6 siteautonomy 1 6 two phase commit and multisite transactions 1 6 architecture
27. Parameters on page 6 6 for information about changing the READONLY setting Configuring the Gateway 6 11 6 12 Oracle Database Gateway for DB2 400 Installation and User s Guide 7 Using the Gateway After installing the gateway you can administer database links access the gateway access AS 400 file members perform distributed queries and copy data between the Oracle database and the AS 400 This chapter contains the following sections Database Link Behavior on page 7 1 Using DB2 400 Cursors on page 7 3 Using the Synonym Feature on page 7 3 Accessing the Gateway on page 7 4 a Accessing OS 400 File Members on page 7 5 Performing Distributed Queries on page 7 6 x Replicating in a Heterogeneous Environment on page 7 8 Copying Data from Oracle Server to DB2 400 Server on page 7 8 Copying Data to Oracle Server from DB2 400 Server on page 7 9 7 1 Database Link Behavior A connection to the gateway is established through a database link when it is first used in a gateway session or transaction In this context connection refers to the connection between the Oracle database and the gateway The connection remains established until the session ends Another session or user can access the same database link but will get a different connection to the gateway and DB2 400 database Connections to the DB2 400 database might be limited by factors that include memory gateway parameters or DB2 400 serv
28. SMALLINT column performing numeric data type operations 9 20 snapshot complete refresh restriction fast refresh snapshots not supported 2 3 older name for Oracle Materialized View see Materialized View 7 8 software requirements 3 2 special authority JOBCTL 4 3 SECADM 4 2 special registers in DB2 400 9 23 SPX IPX protocol implicit protocolconversion 1 4 network transparency 1 3 SOL ANSI standard 1 4 commands 7 2 compatibility with gateway through Oracle integrating server 1 9 constructs processing 9 21 conversion for compatibility withDB2 400 server 1 9 differences 1 8 functions column 9 20 compatibility 9 20 compensated 9 21 scalar 9 20 translated 9 21 ISO standard 1 4 known restrictions 2 5 limitations 2 5 passthrough 9 12 statements gateway appearance to application programs 9 1 passing SQL statements through gateway 9 12 SQL Plus commands COPY copying data from Oracle to DB2 400 7 8 7 8 COPY general restrictions 2 3 COPY limitation 7 9 INSERT limitation 7 9 moving data between databases 1 6 SOLO0105 error Mixed or Graphic String Constant Not Valid 9 16 SQLNET member file name structure 5 3 SQLNET ORA file file name structure 5 3 SOLNET member of ORA file A 2 SOLNET CHG member of ORA file A 2 SOLNET ORA parameter mapped to member SOLNET 5 3 starting the gateway 8 4 statements ALTER SESSION CLOSE DATABASE LINK 7 2 CONNECT TO 7 2 CREATE DATABASE LINK 7 1
29. a version of OS 400 that is currently supported by IBM 3 1 2 Memory For most installations a minimum of 7 MB of virtual memory on the AS 400 side is recommended to support each active user of the Oracle Database Gateway for DB2 400 The total virtual memory requirement for each concurrent use of the gateway depends on the following factors number of concurrent TCP IP connections opened by each user number of data items being transferred between the gateway and the remote transaction program additional factors such as configured network buffer size 3 1 3 CD ROM Drive A CD ROM drive is required 3 1 4 Disk Space Permanent disk space of about 225 MB is required for installation of this product Installation of further cloned instances of the product at the same level use System Requirements 3 1 approximately 76 MB each The actual installation may required an additional 150 MB of temporary disk space These numbers are for initial conditions The disk space can be reduced by running the REMOVEOBS procedure refer to Section 4 5 Postinstallation Steps on page 4 6 After running REMOVEOBS each gateway instance will use 70 MB and the common objects service programs and the National Language files will use approximately 236 MB 3 2 Software Requirements The system software configuration that is described in the following requirements is supported by Oracle as long as the underlying system software
30. executing the CMDORAGTW menu Table 6 1 Command Summary Menu Choice Command Name Purpose 1 CRTORAGTWI creates a copy of an installed gateway version You can copy a version as many times as needed After making a copy use the other gateway commands to change the parameters of the new copy 2 CHGORANET changes the values of network parameters 3 CHGORAPJE changes the parameter values of prestarted jobs 4 CHGORATUN changes the gateway initialization parameters 5 CHGGTWDBG sets or changes values for debugging parameters Use only under the guidance of a representative from Oracle Support Services 6 CRTORADDB submits a batch job to create Oracle data dictionary views based on the DB2 400 system catalog Configuring the Gateway 6 1 Table 6 1 Cont Command Summary Menu Choice Command Name Purpose 7 CHGRECOPRF changes the User Profile name or the User Profile password or both for the User Profile that is associated with transaction recovery For password precautions refer to CHGRECOPRE Change Recovery Profile Parameters on page 6 9 6 1 1 Running the Commands All gateway commands can be accessed through a main menu To invoke the main menu enter ADDLIBLE instance name GO CMDORAGTW where instance name isthe name given to the gateway when it was installed The main menu panel that is illustrated in Example 6 1 CMDORAGTW Oracle Comma
31. files may be added to the ORASRVLIB library 5 Amessage queue with the name of the installation library is created in the QUSRSYS library This is as a result of using the CRTUSRPRF command to create the user profile that corresponds to the installation library 6 Alibrary named ORATEMP will be created to hold intermediate installation objects during the install 1 8 2 SQL Differences Not all SOL implementations are the same The Oracle Database 10g server may support a larger set of built in functions than the databases that are currently being accessed through the gateway Or the semantics of some functions in the DB2 400 server may not agree with an identically named function in the Oracle Database 10g 1 8 Oracle Database Gateway for DB2 400 Installation and User s Guide How the Gateway Works server The SUBSTR function is an example of this because the second and third arguments of SUBSTR in an Oracle Database 10g server may be negative In DB2 400 the second and third arguments of the SUBSTR function cannot be negative In any case the Oracle Database 10g server and the gateway work together to convert the SQL to a form that is compatible with the specific DB2 400 server During this conversion an Oracle Database 10g server function can be converted to a function that is recognizable to the specific DB2 400 server For example the Oracle Database 10g server NVL function is converted to the DB2
32. located in the gateway instance library Member names are referred to as file member name Some of the parameter values in ORA SQLNET can be changed with the CHGORANET command For more information refer to CHGORANET Change Network Parameters on page 6 5 For a list of the network files and members that are provided with the gateway refer to Appendix A Oracle Net Files and Members 5 1 5 1 Trace Files for Job Members To enable tracing for the listener or server use the CHGORANET command to change the values ofthe Listener trace level and Server trace level parameters Possible trace level values are OFF the default provides no trace information Tracing is not enabled USER provides the least detailed trace data ADMIN provides more detailed trace data 16 provides comprehensive trace data Note Follow the advice of Oracle Support Services for setting trace levels Because trace files are large you should use the least detailed trace level possible For more information about changing the trace level parameters refer to CHGORANET Change Network Parameters on page 6 5 When tracing is enabled each job produces a trace file member in the TRC file with a member name in the following format Oracle Net 5 3 SERVxxxxxx for server jobs or LISTxxxxxx for listener jobs where xxxxxx is the job number 5 2 Connecting to the AS 400 through TCP IP Perform the follow
33. mapping error messages 10 1 Materialized View old name was snapshot 7 8 can be used to propagate a complete copy or a subset of non Oracle data 7 8 CREATE MATERIALIZED VIEW command automatically and asynchronously copy DB2 400 server data 7 10 Incremental Materialized View refresh gateway restriction 2 5 member command to display members of ORA file A 2 accessing AS 400 file members 7 5 LISTENER member of ORA file A 2 LISTENLOCK member of ORA file A 2 LOG file A 1 names format 5 3 ORA file A 2 SOLNET 5 3 SOLNET member of ORA file A 2 SOLNET CHG member of ORA file A 2 TRC file A 2 messages and error code processing 10 1 diagnosing 10 2 error 10 1 journaling 9 13 ORA 00947 10 3 Metalink Note 136294 1 connection problems 5 9 missing functionality error categories 10 4 Mobile Agents wireless communication 1 4 monitoring the gateway overview 8 1 starting Oracle gateway monitor 8 1 multiple rows retrieving 6 10 multisite transactions 1 6 N network parameters changing 6 5 NOWAIT option example when not supported 9 22 null values with stored procedures 9 7 numeric column performing numeric data type operations 9 20 numeric data type DB2 400 performs automatic conversions 9 20 NVARCHAR2 data types DB2 400 GRAPHIC support 9 16 NVL function SOL differences 1 9 O observability gateway is shipped with IBM observability it can be removed 4 8 Index 8 OCI rest
34. or any content provided on third party Web sites You bear all risks associated with the use of such content If you choose to purchase any products or services from a third party the relationship is directly between you and the third party Oracle is not responsible for a the quality of third party products or services or b fulfilling any of the terms of the agreement with the third party including delivery of products or services and warranty obligations related to purchased products or services Oracle is not responsible for any loss or damage of any sort that you may incur from dealing with any third party Contents PERA E RARA xi Intended AURA cda xi Documentation Accessibility sse a e E nnne nennen nennen nennen Xi Product Name gei tette ite oti iiti teli itle A eons xii Platform Name ER Nee e e A NE a xii CONVENTIONS 12 2 2 qnie dete ist Ee a e LET d te EE eed ida lagen dd ve ded ep edes xii Related Docunients i nee e ee e ete e etes xii Storage Measurements o enge Len D esa net aee solu e d OU Eoo xiii 1 Introduction 1 1 TORO UC 5 our a ctt e e e ede e ue PR ERE e a 1 1 1 1 1 Protection of Current Investment cccccccscessesseescesceseecscessecsecsseeseeeseseseeseseeeeeeseeseecseesaes 1 1 1 2 10e Release 2 Gateways eee ere preteen i eet ae re Rr ende 1 2 1 2 1 Advantages of the Gateway sssssssseeseee eee nnne nnne nnn nnnnn 1 2 1 2 1 1 Transparency at All Levels rtr eiit id 1
35. products are supported by their respective software vendors Verify the latest support status with your system software vendors 3 2 1 Operating System Requirements This gateway requires any current version of OS 400 that is supported with maintenance by IBM The RUNSQLSTM command that is found in 5722 ST1 SOL Development Kit is needed when creating one instance from another cloning an instance It is not needed for an initial installation from an Oracle distribution medium CD ROM or file 3 2 2 Oracle Integrating Server The Oracle server that is to act as the Oracle integrating server requires the Oracle Database 10g server or Oracle9i server The Oracle integrating server should be running with the latest released patch set for Oracle Database 102 server Oracle9i server or Oracle8i server release 8 1 7 or later 3 3 Documentation Requirements In addition to the manuals supporting the Oracle Database Gateway for DB2 400 Oracle Net and Oracle Database 10g server manuals are recommended You also need the appropriate OS 400 documentation for your system 3 4 Distribution Kit Before installing the gateway verify that you have the correct CD ROM and proper documentation one Oracle Database Gateway for DB2 400 product CD ROM appropriate Oracle documentation for installing administering and using the gateway Oracle Database Gateway for DB2 400 Installation and User s Guide for IBM iSeries OS 400 a Oracle Dat
36. the changes have been committed You can change the isolation level from the default setting CHG to cursor stability CS With cursor stability Users cannot see current changes until the changes have been committed All files that are changed by SQL operations must be journaled 4 6 Oracle Database Gateway for DB2 400 Installation and User s Guide Postinstallation Steps For additional information about changing the isolation level refer to CHGORATUN Change Initialization Parameters on page 6 6 For information about the RR isolation level setting and for more information about CS and CHG isolation level settings refer to the Isolation Level section of the IBM reference for DB2 400 Note When creating a SOL collection DB2 400 creates a journal and a journal receiver within that collection Any files tables that are created within that collection are automatically journaled For this reason it is strongly recommended that you create a SOL collection before installing the Gateway and then install the Gateway into that SOL collection 4 5 2 Step 1 Verify the Gateway Version To verify installation and find out what version of the gateway is running use the following command DSPPGM instance name GTW where instance name isthename that is given to the gateway when it is installed GTW isthe name of the gateway program object the gateway executable program The Text Description lin
37. to meet local demands increase your ability to handle complex customer information or streamline operations your company requires fast access to current and complete information Company growth and diversification often result in geographically scattered data that is processed with a collage of applications that may be using incompatible networks platforms and storage formats Diverse application standards and storage formats can make integration of information difficult For many years Oracle has offered integration technologies to overcome these technical barriers Database Gateways simplify complex systems and remove obstacles to information flow thereby providing your company the opportunity to focus on business 1 1 1 Protection of Current Investment Oracle Database Gateway for DB2 400 gives your company the ability to develop its information systems without forfeiting your investments in current data and applications The gateway gives you access to your Oracle and DB2 400 data with a single set of Oracle applications while you continue to use existing IBM applications to access the IBM data You can also use more productive database tools and move to a distributed database technology without giving up access to your current data If you choose to migrate to Oracle Database technology and productivity then the gateway allows you to control the pace of your migration As you transfer applications Introduction 1 1 from your prev
38. traffic For example instead of sending individual SOL statements across the network an application can send a single EXECUTE command to begin an entire PL SQL routine 1 2 3 2 Native DB2 400 Stored Procedures The gateway can execute DB2 400 stored procedures using standard Oracle PL SQL The Oracle application executes the DB2 400 stored procedure as if it were an Oracle remote procedure Note If you are going to use DB2 400 stored procedures then you must journal the ORACLE2 PC table 1 2 3 3 Native DB2 400 Stored Functions User Defined Functions The gateway can execute DB2 400 User Defined Functions using standard Oracle PL SQL In addition a User Defined Function may be executed within a SOL statement In both cases the Oracle application executes the DB2 400 User Defined Function as if it were a remote Oracle stored function When the User Defined Function is executed as part of a SELECT statement it may not cause any database changes on the remote DB2 400 database 1 2 4 Languages Any application or tool that supports the Oracle Database 10g server can access over 30 different data sources through the Oracle gateways A wide variety of open system tools from Oracle and from third party vendors can be used even if the data is stored in legacy proprietary formats Hundreds of tools are supported including ad hoc query tools Web browsers turnkey applications and application development
39. variable length character strings The Oracle database performs character string comparisons concatenations and sorts by using variable length character string representations The Oracle database never pads or truncates character strings The Oracle database also automatically converts among numbers character strings and dates when necessary 9 8 4 Converting Character String Data Types The gateway binds character string data values from host variables as fixed length character strings The bind length is the length of the character string data value This conversion is performed on every bind DB2 400 performs automatic conversions to the character string data type for example fixed length and variable length of the destination column You have no control over the conversion The conversion might be independent of the data type of the destination column in the database For example UPDATE EMP AS400 SET ENAME BLAKE WHERE EMPNO 7654 If ENAME is a CHAR 9 fixed length column in the ENAME table then the update inserts BLAKE as the last name of employee number 7654 BLAKE is padded with four trailing spaces by the host database to make it conform to the fixed length definition of the column The gateway returns data bound in the manner that is requested by the application For character strings the format is fixed length string The DB2 400 VARCHAR data type can be from 1 to 32740 bytes in length This data
40. various checklists required while installing the gateway 4 2 1 Preinstallation Checklist Step 1 Log On Step 2 Verify System Value for the OCRTAUT Parameter Installing the Gateway 4 1 4 2 2 Installation Checklist Step 1 Mount the CD ROM Step 2 Start the Installation Process Step 3 Set Required Parameters Step 4 Verify Installation Step 5 Finish the Installation a Step 6 Start the Gateway Subsystem 4 2 3 Postinstallation Checklist Step 1 Verify the Gateway Version Step 2 Verify Journaling a Step 3 Journal the ORACLE2PC File Step 4 Configure Oracle Net Step 5 Remove Observability Step 6 Raise the Gateway Priority Level 4 3 Preinstallation Steps Before installing the gateway you must perform the following steps 4 3 1 Step 1 Log On Log on with a user profile that has the following special authorities SECADM JOBCTL ALLOBJ and SYSCFG User profile OSECOFR as distributed by IBM has these authorities 4 3 2 Step 2 Verify System Value for the QCRTAUT Parameter Ensure that the system value for the OCRTAUT parameter is not EXCLUDE To find the current system value enter the following statement from the command line WRKSYSVAL OCRTAUT If the system value is EXCLUDE then change it to any other less restrictive value in order to allow installation of the gateway and remember to change QRCTAUT back to EXCLUDE after installing the gateway 4 4
41. 1 7 array processing 9 2 call stack displaying 8 4 changes made to the AS 400 system 1 8 co existence with previous gateways 4 1 commands main topic 6 1 CHGGTWDBG isolating cause of suspected gateway problem 6 8 CHGORANET changing language settings B 1 CHGORANET changing network parameters 6 5 CHGORANET changing parameters A 2 CHGORAPJE changing prestarted job parameters 6 6 CHGORATUN changing BLOCKSIZE default value 6 11 CHGORATUN changing initialization parameters authority required 6 6 CHGORATUN changing language settings gateway configuration B 1 CHGRECOPRE changing recovery user password 6 4 CHGUSRPRE changing recovery user password in OS 400 6 4 CRTORAGTWI creating a gateway instance copy 6 3 menu to access 6 2 running 6 2 summary of commands 6 1 components database gateway 1 7 DB2server 1 7 Oracle Database 10g Server 1 7 default configuration as commit point site 1 6 development applications 9 1 Globalization Support configuration B 1 how it works 1 7 initialization parameters 6 7 job locks 8 4 jobs 7 4 Index 6 known restrictions 2 3 library files A 1 multiple copies 6 3 no continuously running background processes 1 7 post installation 4 6 raising priority level 4 9 removing observability 4 8 retrieving data 6 10 security 8 4 setting optional parameters 6 10 starting 8 4 stopping 8 4 transparency levels 1 2 two phase commit processing protocol limitati
42. 1 7 database triggers 1 3 differences from DB2 400 9 22 distributed capabilities 1 3 distributed query optimization 1 3 extended database services 1 3 mapping DB2 400 error messages 10 1 performing character string operations 9 18 SQL extended database services 1 3 stored procedures 1 3 triggers copying data from Oracle server 7 8 replicating in a distributed environment 7 8 two phase commit protection 1 3 Oracle Database server and array size 9 3 and error detection 10 2 copying data to DB2 400 server 7 8 date and time operations 9 18 initialization parameter notes 6 7 Oracle DATE data type 9 19 Oracle Developer Forms compatibility 9 24 Oracle gateway monitor display gateway callstack 8 4 display open files 8 4 ORAMON command 8 1 overview 8 1 starting 8 1 starting and stopping gateway 8 3 starting TCP IP listener 8 4 working with gateway job locks 8 4 working with gateway jobs 8 3 Oracle integrating server and GLOBAL NAMES initialization parameter 2 5 function regarding SOL 1 9 Oracle Net chapter 5 1 API 5 1 communications 5 2 configuration supported by Oracle server 1 6 configuring 4 8 configuring APPC LU6 2 define physicalline 5 5 configuring TCP IP checklist 5 6 define local host name 5 7 for TCP IP AS 400 5 6 modify port number 5 6 start the listener 5 8 verify host name 5 8 connecting TCP IP specify SID names 5 4 connecting TCP IP add connect descriptor 5
43. 10 DUAL description C 10 examining available database links 7 3 OTGREGISTER description C 10 OTGREGISTER finding primary authorization ID being used by gateway 9 23 TABLE PRIVILEGES description C 11 USER CATALOG description C 11 USER COL COMMENTS description C 11 USER CONS COLUMNS description C 11 USER CONSTRAINTS description C 12 USER DB LINKS examining available databaselinks 7 3 SER INDEXES description C 12 SER OBJECTS description C 14 SER SYNONYMS description C 14 SER TAB COLUMNS description C 15 SER TAB COMMENTS description C 16 SER TABLES description C 16 SER USERS description C 17 SER VIEWS description C 18 U U U U U U U U data types conversion for Oracle tools 9 14 from DB2 400 to Oracle B 5 Oracle to DB2 400 data types 9 14 Index 4 converting character strings 9 18 DATE performing Date and Time operations 9 18 DB2 400 GRAPHIC 9 16 IBM DATE Oracle DATE data types and IBM DATE data types are mapped to each other 9 19 LONG converting character string data types 9 18 known restriction 2 6 LONG VARCHAR 9 18 Oracle DATE Oracle DATE data types and IBM DATE data types are mapped to each other 9 19 restrictions 9 14 TIME performing Date and Time operations 9 18 TIMESTAMP performing Date and Time operations 9 18 VARCHAR DB2 data type converted to Oracle VARCHAR 2 data type 9 18 zoned decimal 9 23 DATABASE DOMAIN parameter 6 7 database link acc
44. 4 checklist 5 4 5 5 to AS 400 5 4 connections Oracle Net for AS 400 architecture 5 2 creating databaselinks 7 2 distributed database 5 2 distributed processing 5 1 filenames 5 3 listener 5 2 LOG file members A 1 ORA file members A 2 overview 5 1 TCP IP listener default trace output file A 2 terminology client 5 2 driver 5 2 host 5 2 network 5 2 protocol 5 2 terminology main topic 5 2 tracing job members 5 3 TRC file members A 2 Oracle Precompilers restriction 2 5 Oracle subsystem error reporting 10 4 Oracle Support Services contacting 10 3 ORACLE2PC file journaling two phase commit transactions 4 8 recording two phase commit transactions 7 7 table using DB2 400 stored procedures 1 5 ORADBMBOPT Data Area or Environment variable 9 17 ORAMON command starting the gateway 1 7 starting the Oracle gateway monitor 8 1 ORARAW data area 6 10 ORASRVLIB library creation 1 8 OTGREGISTER view description C 10 finding primary authorization ID being used by gateway 9 23 outer joins SOL functions 1 3 P PACKED DECIMAL column performing numeric data type operations 9 20 panel examples Change Oracle Gateway Debugging Option 6 8 Change Oracle Gateway Initialization Parameters page 1 6 7 Change Oracle Gateway Initialization Parameters page 2 6 8 Change Oracle Network Parameters 6 5 Change Oracle Prestart Parameters 6 6 Change Recovery Profile Parameters 6 9 Change TCP IP D
45. 4 Oracle Database Gateway for DB2 400 Installation and Users Guide Normally WE81S08859P1 is the default CHARACTER SET for Western European languages including English on non EBCDIC platforms Other languages may get a different default character set for example KO16KSC5601 for Korean B 5 Message Availability Availability of the supported language message modules depends on which modules are installed in the Oracle product set that is running on the server If you do not have message modules installed for a particular language set then specifying that language with a language parameter results in no messages being displayed for that module in the requested language Only a generalized and rather uninformative message will be provided B 6 DB2 400 GRAPHIC Support When converting DB2 400 data types to Oracle data types if support for DB2 400 GRAPHIC data types GRAPHIC VARGRAPHIC or LONG VARGRAPHIC is required then special consideration must be given to the selection of the NLS_LANG character set Refer to DB2 400 GRAPHIC Support on page 9 16 for more information B 7 Character Set Expansions If youuse a CREATE TABLE x AS SELECT command in Oracle and the source table of the CREATE TABLE is a table in DB2 400 you may be surprised as to the width of the character type columns of the new table in the Oracle DB This column expansion is due to the fact that all codepoints in the CCSID of the DB2 400 column nee
46. 400 This is a software prerequisite if one Gateway instance is cloned from another 2 4 1 2 Selecting CHAR Column Returns Width Doubled Selecting CHAR VARCHAR column returns its width doubled To correct the problem reported in bug number 2451535 create the data area named ONLS_MINNB and set its value to 2 as in the following command CRTDTAARA instlib ONLS MINNB TYPE CHAR LEN 10 value 2 where instlib is the installation library 2 4 1 3 Coexistence with Previous Version Gateway Data Dictionary Tables After the data dictionary tables are installed by a 10g Release 2 Gateway do not reinstall the data dictionary tables from a previous version gateway or you will not receive the benefits of the full capabilities of 102 Release 2 2 4 1 4 Oracle Fast Refresh Snapshots Oracle fast refresh snapshots are not supported between the gateway and the Oracle Database 10g However Oracle complete refresh snapshots are supported between the gateway and the Oracle Database 10g server 2 4 1 5 Oracle SQL Command INSERT When copying data from an Oracle Database 10g server to a DB2 400 server the Oracle SQL command INSERT is not supported The SOL Plus COPY command must be used Refer to SOL Plus COPY Command on page 7 9 for more information 2 4 1 6 Verify the QCRTAUT Parameter Setting Before Installation Before installing the gateway verify that the OCRTAUT parameter is set to allow installation not set to EXCLUDE Refe
47. 400 VALUE function Alternatively the Oracle integrating server withholds functions that are not executable by the DB2 400 server and performs those functions on the Oracle server side after the rows are fetched from the DB2 400 server This is called post processing because it occurs after the rows are fetched from DB2 400 This post processing generally applies to SELECT statements The SUBSTR function when used with two or three parameters is an example of a function that is always post processed on the Oracle server side since the semantics of the SUBSTR function with two or thee parameters differs between Oracle 10g and DB2 400 The Oracle integrating server and the gateway cannot perform this kind of manipulation on UPDATE INSERT or DELETE statements because doing so changes transaction semantics 1 8 3 Heterogeneous Services Architecture This release of the Oracle Database Gateway for DB2 400 uses the Oracle Heterogeneous Services component within the Oracle Database 10g server For detailed information about heterogeneous services refer to the Oracle Database Heterogeneous Connectivity Administrator s Guide Introduction 1 9 1 10 Oracle Database Gateway for DB2 400 Installation and Users Guide 2 Release Information This chapter describes the changes and corrected problems in this release It contains the following sections ProductSet on page 2 1 Changes and Enhancement
48. 4865825 Gateway listener hangs Bug 4566044 SQL0804 when calling DB2 400 stored procedure with DECIMAL IN parameter Bug 4473636 NLS LANG CZECH CZECH REPUBLIC EE8EBCDIC870 not accepted by GTW Bug 4414487 New DataDictionary Definitions for Streams replication to accompany fix for generic bug 3653933 Bug 4338891 Column lengths get increased three times and padded with blanks in Oracle UTF8 DB Bug 4333999 CRTORAGTWI fails with CPFA0A9 copying timezone dat Bug number 4307761 Dead connection detection is not working when client connection is broken Bug number 4093610 CHAR data padded with multibyte blank Bug number 4085764 MCH0601 F QC2STRMI STRSTR 7 T HOADB2 HOAParS 26 during INSERT Bug number 4019083 MCH0601 space offset amp 2 or amp 9 is outside current limit for object Bug number 3985615 ORA 0004 homtiap initAgentProcess returns non zero return code 1804 for CCSID 930 Bug number 3975301 SQL0901 on second and subsequent calls to stored procedure with no parameters check IBM APAR SE18413 Bug number 3907455 ORA 01017 on logon under V5R3M0 Bug number 3803899 Problems when cloning a new instance or when running a cloned instance Bug number 3755039 After upgrading to OS 400 V5R3 TG4DB2400 returns ORA 01017 Bug number 3699834 When duplicating data via SQL Plus COPY command the length of copied data is incorrect Bug nu
49. 5 6 connecting to the AS 400 5 4 connection flow 5 6 connection problems troubleshooting 5 8 inherit mechanism 5 6 listener gateway architecture 1 7 starting 8 4 local domain name 5 7 local host name 5 7 port numbers cloning a gateway instance 6 4 configuring for Oracle Net listener 5 7 name choices on install panel 4 5 protocol implicit protocol conversion 1 4 network transparency 1 3 socket interface 4 3 starting the listener 8 4 terminology Oracle Net client 5 2 driver 5 2 host 5 2 network 5 2 Index 12 protocol 5 2 TIME data type CHAR format 9 19 performing Date and Time operations 9 18 TIMESTAMP data type CHAR format 9 19 performing Date and Time operations 9 18 TNS connect descriptor 7 2 TNSNAMES ORA file add a TCP IP connect descriptor 5 4 connect descriptor 7 2 specify a system identifier SID name 5 4 TO_CHAR function Julian dates support 2 4 TO_DATE function dates in the 21st century 9 19 Julian dates support 2 4 performing date and time operations 9 19 trace files agent trace files will appear if requested 1 8 for job members file name structure 5 3 trace file member names format 5 3 TRANSLATE translated SQL function 9 21 translated SQL functions defined 9 21 transparency access method 1 3 data storage 1 3 location 1 2 network 1 3 operating system 1 3 TRC file member name format 5 3 members in TRC file A 2 trace files for job members
50. A SOLNET file Refer to CHGORANET Change Network Parameters on page 6 5 for more information A 1 2 6 Displaying File Members To display these members on the AS 400 use the following command WRKMBRPDM instance name ORA where instance name is the name that is used for the gateway when it is installed You may also use the command WRKTRCLOG but you must first make sure that the instance library is in the library list before using this command A 1 3 TRC File Members The TRC file contains the following members where xxxxxx is numeric the job number a LISTxxxxxx is the default trace output file for the Oracle Net TCP IP listener where xxxxxx isthejob number of the listener SERVxxxxxx is the Oracle Net trace for server job number xxxxxx To display these members on the AS 400 use the following AS 400 command WRKMBRPDM instance name TRC A 2 Oracle Database Gateway for DB2 400 Installation and User s Guide where instance name is the name that is used for the gateway when it is installed Alternatively you may use the command WRKTRCLOG but you must first make sure that the instance library is in the library list before using this command A 1 4 ORA files in the Integrated File System IFS There is a ORA file CALL NAMES ORA in the IFS This file is used in conjunction with DB2 400 SQL User Defined Functions UDF that are to be called through the gateway Oracle must be aware of all the DB2 400 UDFs
51. ATALOG USER INDEXES on page C 12 USER OBJECTS on page C 14 USER SYNONYMS on page C 14 USER TAB COLUMNS on page C 15 USER TAB COMMENTS on page C 16 USER TABLES on page C 16 USER USERS on page C 17 USER VIEWS on page C 18 The column names that are listed for the following views are the column names that the Oracle database presents to the client and they may actually differ from the underlying views in DB2 400 C 1 ALL CATALOG All tables views synonyms and sequences that are accessible to the user Column Name Description OWNER TABLE NAME TABLE TYPE Schema of the object Name of the object Type of object C 2 ALL COL COMMENTS Comments on columns of accessible tables and views Column Name OWNER TABLE NAME COLUMN NAME COMMENTS Description Schema of the object Object name Column name Comments on the column C 3 ALL CONS COLUMNS Information about columns in constraint definitions for all users Similar to USER CONS COLUMNS on page C 11 Column Name OWNER CONSTRAINT NAME TABLE NAME COLUMN NAME POSITION Description Schema of the object Name associated with the constraint definition Object name Column name Original position of the column in the definition C 2 Oracle Database Gateway for DB2 400 Installation and Users Guide C 4 ALL CONSTRAINTS Constraint definitions on accessibl
52. An attempt to use a UDF that does not appear in this file will result in an ORA 06571 error The file is located at home myinst rdbms admin call_names ora where myinst is the name of your gateway instance The file may be edited with the EDTF system editor The names of the UDFs are found in this file at most one name in each line The name may be qualified by the name of the library in which a UDF resides e g MYORAINST MYFUNC A 2 Agent Trace Files Agent trace files are now located within the integrated file system in the directory home myinst rdbms log where myinst is the instance name The file names are of the form myinst agt nnnnnn trc where myinst is the instance name and nnnnnn is the job number To display these files use the WRKTRCLOG command but ensure that the instance library is in the library list before doing so Oracle Net Files and Members A 3 Agent Trace Files A 4 Oracle Database Gateway for DB2 400 Installation and User s Guide Globalization Support This appendix documents the Globalization Support information for the Oracle Database Gateway for DB2 400 For more information about using Globalization Support refer to Oracle Database Application Developer s Guide Fundamentals This appendix contains the following sections Overview of Globalization Support on page B 1 Gateway Configuration on page B 1 DB2 400 Coded Character Set Considerations on page B 2 Oracle Server
53. B2 400 servers into appropriate Oracle data types before passing these values back to the application or Oracle tool The following table lists the data type mapping and restrictions Table 9 1 Data Type Mapping and Restrictions DB2 400 Oracle Criteria Comments CHAR N CHAR N 1 lt N lt 255 Maximum length of a DB2 400 CHAR VARCHAR2 N 255 lt N lt 4000 column is 32766 characters LONG 4000 lt N VARCHAR N VARCHAR2 N N lt 4000 Maximum length of a DB2 400 LONG 4000 lt N 332740 VARCHAR column is 32740 characters LONG VARCHAR N VARCHAR2 N N lt 4000 Maximum length of a DB2 400 LONG 4000 lt N 332740 VARCHAR column is 32740 characters VARCHAR N RAW N 1 lt N lt 255 Maximum length of for Bit Data a DB2 400 LONG RAW N 255 lt N 32740 VARCHAR column is 32740 characters 9 14 Oracle Database Gateway for DB2 400 Installation and User s Guide Converting DB2 400 Data Types to Oracle Data Types Table 9 1 Cont Data Type Mapping and Restrictions DB2 400 Oracle Criteria Comments DATE DATE Refer Refer to to Performing Performing Date Date and Time and Time Operations on Operations on page 9 18 page 9 18 TIME CHAR 8 ROWID RAW 40 TIMESTAMP CHAR 26 See note below table TIMESTAMP TIMESTAMP Based on ORATIMSTMP environment
54. B2400 INPUT UPDATE EMP SET SAL REUSLT WHERE ENAME INPUT END Note Thecallto the REVISE SALARY FUNC function may also be done in a SELECT statement That SELECT statement would be S RI LECT MYLIB REVISE_SALARY_FUNC DB2400 INPUT INTO SULT FROM OTGDB2 DUAL DB2400 Gl Lu When the gateway receives a call to execute a DB2 400 stored function it first does a lookup of the function name in the QSYS2 SYSFUNCS and QSYS2 SYSPARMS DB2 400 system tables to determine the following The stored function to execute The gateway retrieves information from the ROUTINE SCHEMA and ROUTINE NAME columns of the QSYS2 SYSFUNCS table in order to locate the actual DB2 400 stored function code that is to be invoked by the gateway Multiple DB2 400 stored functions can have the same ROUTINE NAME but they must be uniquely identified by the combination of the ROUTINE SCHEMA and ROUTINE NAME columns of the QSYS2 SYSFUNCS table The gateway always receives a qualified stored function name from the Oracle Database server and executes the DB2 400 stored function by using this qualified name If a qualified name is not explicitly identified by the application the user ID that is passed over the database link is used to match the value in ROUTINE SCHEMA To ensure that you execute the correct DB2 400 stored function Oracle recommend
55. BLOCKS N A NUM FREELIST BLOCKS N A DEGREE N A INSTANCES N A CACHE N A TABLE LOCK N A SAMPLE SIZE N A LAST ANALYZED N A PARTITIONED N A IOT TYPE N A TEMPORARY N A SECONDARY N A NESTED N A BUFFER POOL N A ROW MOVEMENT N A GLOBAL STATS N A USER STATS N A DURATION N A SKIP CORRUPT N A MONITORING N A CLUSTER OWNER N A DEPENDENCIES N A COMPRESSION N A DROPPED N A C 29 USER USERS Information about the current user Column Name Description USERNAME USER ID Name of the user N A Data Dictionary Views C 17 USER VIEWS Column Name Description ACCOUNT STATUS N A LOCK DATE N A EXPIRY DATE N A DEFAULT TABLESPACE N A TEMPORARY TABLESPACE N A CREATED N A INITIAL RSRC CONSUMER GR N A OUP EXTERNAL NAME N A C 30 USER VIEWS Text of views that are owned by the user Column Name Description VIEW NAME Name of the view TEXT LENGTH Length of the view text TEXT First line of view text TYPE TEXT LENGTH N A TYPE TEXT N A OID TEXT LENGTH N A OID TEXT N A VIEW TYPE OWNER N A VIEW TYPE N A SUPERVIEW NAME N A C 18 Oracle Database Gateway for DB2 400 Installation and User s Guide A accessing AS 400 file members 7 5 Advanced Security 1 4 agent trace files Oracle net feature A 3 system changes when installing g
56. CORPDATA with members MBR1 MBR2 and MBR3 then issuing the following SOL statement results in an error SELECT FROM CORPDATA EMP MBR1 If your OS 400 file has multiple members then you can use OS 400 logical files to point to some or all of the members To access a file member create a logical file over the physical file members Inthe physical file data members parameter DTAMBRS of the CRTLF command specify which members you want to access Using the previous example if you wanted to access members MBR1 and MBR2 of EMP then you would enter the following command CRTLF FILE CORPDATA EMPLF DTAMBRS CORPDATA EMP MBR1 MBR2 SRCFILE myinst QDDSSRC SRCMBR EMPLF where member EMPLF in file myinst QDDSSRC is as follows A ACCESS TWO DIFFERENT MBRS IN AN EMP FILE USING A LOGICAL A FILE THE TWO MEMBERS ARE NAMED EXTERNALLY ON CRTLF COMMAND A R EMPTABLE PFILE CORPDATA EMP A K EMPNO This creates a logical file EMPLF that points to MBR1 and MBR2 ofthe EMP file The SQL statement SELECT FROM CORPDATA EMPLF will return all of the rows that are contained in MBR1 and MBR2 You can also specify DTAMBRS ALL to include all members of a physical file in a logical file Note To insert into a specific member you must create a logical file for that member 7 5 1 Accessing Flat Files Flat files are OS 400 files that are created by a facility ot
57. CPIC trace Itis also used for debugging the connection between the listener and the server Refer to Viewing Jobs on page 7 4 for instructions on viewing jobs and a description of the jobs that typically exist 5 3 Checklists for Configuring Oracle Net The following sections include checklists for configuring Oracle Net and for connecting to an Oracle server 5 3 1 Checklist for Configuring Oracle Net for TCP IP Step 1 Configure the Port Number Step 2 Define the Host Name a Step 3 Verify the Host Name Step 4 Verify that the Listener is Started 5 3 2 Connecting Through TCP IP on the Oracle Server Checklist Step 1 Add a TCP IP Connect Descriptor to tnsnames ora Step 2 Specify SID Names 5 3 3 Preliminary Step Define a Physical Line This step is required only if no physical connection currently exists between the Oracle Database 10g server and the AS 400 To define the physical connection use the CRTLI connection NETH command to create an ethernet Oracle Net 5 5 When you use the CRTLINETH command you can set the AUTOCREATE CONTROLLER parameter to YES the AS 400 system default is NO This enables the AS 400 auto configuration feature If a line is already defined then you can usethe DSPLIND line name command to display the line description parameters These line description parameters are used to configure the network on the computer where the gateway resid
58. Considerations for Using Stored Procedures with DB2 400 cece 9 7 Using Oracle Stored Functions with the Gateway sse eee 9 8 Using DB2 400 Stored Functions with the Gateway 9 9 Executing DB2 400 Stored Functions from Applications sss 9 10 vii 10 viii 9 6 2 Considerations for Using Stored Functions with DB2 400 sss 9 11 9 7 Passing DB2 400 SQL Statements Through the Gateway sess 9 12 9 7 1 Using the DBMS HS PASSTHROUGH EXECUTE IMMEDIATE Function 9 12 9 7 2 Examples cai goes en ete eate ti tn e 9 13 9 7 3 Using Passthrough Statements to Create Tables sss 9 13 9 7 4 Retrieving Results Sets Through Passthrough eese 9 13 9 7 4 1 Example 3 59 pub Entire tio as PEL VE ias 9 13 9 8 Converting DB2 400 Data Types to Oracle Data Types sess 9 14 9 8 1 DB2 400 GRAPHIC S ppott titre irre ee eher eret 9 16 9 8 2 Fot DBCS Sets ee 15 eet oe eR e ro det e ore ea ere de eee aee eee e en 9 16 9 8 2 1 Double Byte Character Support esses enne 9 16 9 8 3 Performing Character String Operations sse eene 9 18 9 8 4 Converting Character String Data Types esses eee 9 18 9 8 5 Performing Date and Time Operations sse eee nennen 9 18 9 8 6 Datesin the 21st Century osito arte tiere HP en ie c br bees 9 19 9 8 7 Performing Numeric Data Type Op
59. DB2 400 server is identified to the Oracle Database 10g server by using a database link The database link is the same construct that is used to identify other Oracle Database 102 server databases Tables on the DB2 400 server are referenced in SQL as table name8Qdblink name or preferably as library table_name dblink_name Introduction 1 7 If you create synonyms or views in the Oracle Database then you can refer to tables on the DB2 400 server by using simple names as though the tables were local to the Oracle Database 102 server When the Oracle Database 10g server encounters a reference to a table on the DB2 400 server the applicable portion of the SOL statement is sent to the gateway for processing Any host variables that are associated with the SOL statement are bound to the gateway and therefore to the DB2 400 server The gateway is responsible for sending these SOL statements to the DB2 400 server The DB2 400 server is responsible for executing the SOL statements and for detecting errors and returning responses 1 8 1 Gateway Changes to the AS 400 System Installing the gateway on the AS 400 system produces the following changes to the system 1 Alibrary is created with the instance name as given in the first installation panel For an example refer to Example 4 1 Install Oracle Database Gateway Panel on page 4 4 Almost all of the objects needed for this gateway instance will be found in this library wi
60. DBCS 939 5035 Japanese JA16EBCDIC930 930 5026 Korean KO16DBCS 933 Spanish WE8EBCDIC284 284 Thai TH8TISEBCDIC 838 Turkish TR8EBCDIC1026 1026 United States Canada WES8EBCDIC37 37 United States Canada Euro WE8EBCDIC1140 1140 United States Canada WES8EBCDIC37C Western European WES8EBCDIC500 500 Western European Euro WE8EBCDIC1148 1148 Western European WE8EBCDIC500C Western European Euro WE8EBCDIC1148C United Kingdom WE8EBCDIC285 285 United Kingdom Euro WE8EBCDICIC1146 1146 B 3 2 Supported Languages and Territories Oracle Database Gateway for DB2 400 supports the following language and territory combinations Table B 2 Supported Languages and Territories Language Territory American America Brazilian Portuguese Brazil Canadian French Canada Czech Czech Republic Danish Denmark Dutch Netherlands Finnish Finland French France German Germany Globalization Support B 3 Oracle Server and Client Configuration Table B 2 Cont Supported Languages and Territories Language Territory Greek Greece Hungarian Hungary Icelandic Iceland Italian Italy Japanese Japan Mexican Spanish Mexico Norwegian Norway Polish Poland Portuguese Portugal Simplified Chinese China Slovak Slovakia Spanish Spain Swedish Sweden Traditional Chinese Taiwan Turkish Turkey B 4 Oracle Server and Client Configuration
61. E SALARYGDB2400 INPUT RESULT UPDATE EMP SET SAL RESULT WHERE ENAME INPUT END When the gateway receives a call to execute a DB2 400 stored procedure it first does a lookup of the procedure in the QSYS2 SYSPROCS and QSYS2 SYSPARMS DB2 400 system tables to determine the following 1 The stored procedure to execute The gateway retrieves information from the ROUTINE SCHEMA and ROUTINE NAME columns of the QSYS2 SYSPROCS table in order to locate the actual DB2 400 stored procedure code to be invoked by the gateway Multiple DB2 400 stored procedures can have the same ROUTINE NAME but they must be uniquely identified by the combination of the ROUTINE SCHEMA and ROUTINE NAME columns of the QSYS2 SYSPROCS table 9 6 Oracle Database Gateway for DB2 400 Installation and User s Guide Using DB2 400 Stored Procedures with the Gateway The gateway always receives a qualified stored procedure name from the Oracle database and executes the DB2 400 stored procedure by using this qualified name If a qualified name is not explicitly identified by the application then the user ID that is passed over the database link is used to match the value in ROUTINE SCHEMA To ensure that you execute the correct DB2 400 stored procedure Oracle recommends that you use fully qualified stored procedure names when invoking DB2 400 stored procedures from Oracle PL SQL The parameter list of the stored procedure When a DB2 400 stor
62. E TABLE EMP AS SELECT FROM SCOTT EMP gateway a Use the INSERT command to copy data from the DB2 400 server to the Oracle Database INSERT INTO oracle table SELECT FROM db2table gateway The following example selects all rows from the EMP table on the DB2 400 server and inserts them into the local Oracle EMP table Using the Gateway 7 9 INSERT INTO EMP SELECT FROM SCOTT EMP gateway a Usethe CREATE MATERIALIZED VIEW command to automatically and asynchronously copy DB2 400 server data into the Oracle database The complete refresh capability can be used to propagate a complete copy or a subset For more information about creating materialized views refer to the Oracle Database SQL Reference To create a copy CREATE MATERIALIZED VIEW empdb2 PCTFREE 5 PCTUSED 60 TABLESPACE users STORAGE INITIAL 50K NEXT 50K REFRESH COMPLETE NEXT SYSDATE 1 WITH ROWID AS SELECT FROM SCOTT EMP gateway The following example creates a materialized view of data that is refreshed every day after the first refresh If you require only a subset of the AS 400 data then a WHERE clause is added as in the following example CREATE MATERIALIZED VIEW empdb2 PCTFREE 5 PCTUSED 60 TABLESPACE users STORAGE INITIAL 50K NEXT 50K REFRESH COMPLETE NEXT SYSDAT WITH ROWID AS LH Es SELECT FROM SCOTT EMP gateway WHERE deptno 20 a Use the SOL Plus COPY command to co
63. ERNMENT RIGHTS Programs software databases and related documentation and technical data delivered to U S Government customers are commercial computer software or commercial technical data pursuant to the applicable Federal Acquisition Regulation and agency specific supplemental regulations As such use duplication disclosure modification and adaptation of the Programs including documentation and technical data shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement and to the extent applicable the additional rights set forth in FAR 52 227 19 Commercial Computer Software Restricted Rights June 1987 Oracle USA Inc 500 Oracle Parkway Redwood City CA 94065 The Programs are not intended for use in any nuclear aviation mass transit medical or other inherently dangerous applications It shall be the licensee s responsibility to take all appropriate fail safe backup redundancy and other measures to ensure the safe use of such applications if the Programs are used for such purposes and we disclaim liability for any damages caused by such use of the Programs Oracle JD Edwards PeopleSoft and Siebel are registered trademarks of Oracle Corporation and or its affiliates Other names may be trademarks of their respective owners The Programs may provide links to Web sites and access to content products and services from third parties Oracle is not responsible for the availability of
64. F command accessing OS 400 file members 7 5 CRTORADDB command description 6 9 purpose 6 1 CRTORAGTWI command description 6 3 instructions 6 3 purpose 6 1 CRTUSRPRF command creating user profile message queue created in QUSRSYS library 1 8 cursor stability journaling 4 6 cursors for DB2 400 maximum number 7 3 Index 3 data control language DCL issue native DCL from Oracle environment 1 5 data conversion setting optional parameters 6 11 data definition language DDL Oracle applications use native DDL 1 5 data dictionary create gateway installation panel option 6 5 creating views batch job 6 9 emulation in DB2 400 server 9 23 installation and support 4 5 post installation steps of gateway 4 6 tables restriction with v4 gateways 2 3 using 9 23 views ALL CATALOG description C 2 ALL COL COMMENTS description C 2 ALL CONS COLUMNS description C 2 ALL CONSTRAINTS description C 3 ALL DB LINKS examining available database links 7 3 ALL IND COLUMNS description C 3 ALL INDEXES description C 4 ALL OBJECTS description C 5 ALL SYNONYMS description C 6 ALL TAB COLUMNS description C 6 ALL TAB COMMENTS description C 7 ALL TABLES description C 7 ALL USERS description C 9 ALL VIEWS description C 9 augmenting DB2 400 database catalogs 9 23 COLUMN PRIVILEGES description C 9 DBA DB LINKS accessible only to users with DBA authorization 7 3 DICTIONARY description C
65. LL INDEXES Description of indexes on tables that are accessible to the user Column Name Description OWNER Owner of the index INDEX NAME Name of the index INDEX TYPE Type of the Index TABLE OWNER Owner of the indexed object TABLE NAME Name of the indexed object TABLE TYPE Type of indexed object UNIQUENESS Uniqueness status of the index COMPRESSION N A PREFIX LENGTH N A TABLESPACE NAME Name of the table space containing the index INI TRANS N A MAX TRANS N A INITIAL EXTENT N A NEXT EXTENT N A MIN EXTENTS N A MAX EXTENTS N A PCT INCREASE N A PCT THRESHOLD N A INCLUDE COLUMN N A FREELISTS N A FREELIST GROUPS N A PCT FREE N A LOGGING N A BLEVEL Depth of the index from its root block to its leaf LEAF BLOCKS DISTINCT KEYS AVG LEAF B OCKS P ER K EY AVG DATA BLOCKS P ER K EY CLUSTERING FACTOR STATUS NUM ROWS blocks A depth of one indicates that the root block and the leaf block are the same Number of leaf blocks in the index Number of distinct indexed values For indexes enforcing UNIQUE and PRIMARY KEY constraints this value is the same as the number of rows in the table N A N A N A State of the index VALID N A C 4 Oracle Database Gateway
66. ME TABLE NAMFE Ly COLUMN NAMI POSITION Owner of the constraint definition Name associated with the constraint definition Name associated with the table with the constraint definition Name associated with the column specified in the constraint definition Original position of the column in the definition C 22 USER CONSTRAINTS Constraint definitions on user tables Column Name Description OWNER CONSTRAINT NAME CONSTRAINT TYPE TABLE NAMF SEARCH CONDITION R OWNER R CONSTRAINT NAME ELETE RULE RRABLE EFERRED lt GU U WH y ny Dp p i DATED E TED t Q E z D Lu ES Fe LAST_CHANGE INDEX_OWNER INDEX_NAME INVALID VIEW_RELATED Owner of the constraint definition Name associated with the constraint definition Type of constraint definition Name associated with the table with the constraint definition Text of the search condition for the table check Owner of the table used in the referential constraint Name of the unique constraint definition for the referenced table Delete rule for the referential constraint Status of the constraint N A N A N A N A N A N A N A N A N A N A N A C 23 USER_INDEXES Description of the user s own indexes Column Name Description INDEX_NAME Name of t
67. ORA 3114 5 6 2 3 TCP IP Configuration TCP IP configuration under AS 400 Option 10 of CFGTCP Work with TCP IP Host Table Entries on page 5 10 is not configured as anticipated by the gateway listener The gateway listener requires host_name domain_name in the TCP IP host table entries If you do not specify this then you will not be able to get any response back from the gateway You will be able to see the connection in the AS 400 but no data can be transferred 5 6 2 4 Gateway Listener is Not Up By default the gateway listener is automatically started when you start the gateway instance You can manually start the listener by executing the command STRORALSN 5 6 3 ORA 28511 The following are some causes of the error 5 6 3 1 Gateway or Listener is Not Up Check to see that the gateway is up and running correctly and is properly configured Check to see if the listener is up 5 6 3 2 Set Fully Qualified host domain Name Ensure that a fully qualified host domain name has been set on the AS 400 Use CFGTCP to do this Use Option 10 on page 5 10 and Option 12 on page 5 10 to set a fully qualified host domain name Use ping to verify that the host domain name matches the IP address of the host name Oracle Net 5 9 5 6 4 ORA 28500 Check any accompanying message from the AS 400 to determine what the problem might be or check the AS 400 job log RUNORAGTTP job log For example message SQL 7008 indicates that the
68. ORACLE Oracle Database Gateway for DB2 400 Installation and User s Guide 10g Release 2 10 2 for IBM iSeries OS 400 B16222 02 August 2007 Oracle Database Gateway for DB2 400 Installation and User s Guide 10g Release 2 10 2 for IBM iSeries OS 400 B16222 02 Copyright 2002 2007 Oracle All rights reserved Primary Author Maitreyee Chaliha The Programs which include both the software and documentation contain proprietary information they are provided under a license agreement containing restrictions on use and disclosure and are also protected by copyright patent and other intellectual and industrial property laws Reverse engineering disassembly or decompilation of the Programs except to the extent required to obtain interoperability with other independently created software or as specified by law is prohibited The information contained in this document is subject to change without notice If you find any problems in the documentation please report them to us in writing This document is not warranted to be error free Except as may be expressly permitted in your license agreement for these Programs no part of these Programs may be reproduced or transmitted in any form or by any means electronic or mechanical for any purpose If the Programs are delivered to the United States Government or anyone licensing or using the Programs on behalf of the United States Government the following notice is applicable U S GOV
69. Oracle Database Net Services Administrator s Guide Oracle Database Net Services Reference a Oracle Database Backup and Recovery Quick Start Guide Oracle Database Backup and Recovery Reference a Oracle Database PL SQL Packages and Types Reference a Oracle Database PL SQL User s Guide and Reference a Oracle Secure Backup Reference Oracle Secure Backup Administrator s Guide Oracle Secure Backup Installation Guide a SQL Plus Quick Reference a SQL Plus User s Guide and Reference IBM Books Refer to the IBM documents for your platform and operating system Storage Measurements Storage measurements use the following abbreviations KB for kilobyte which equals 1024 bytes MB for megabyte which equals 1 048 576 bytes xiii GB for gigabyte which equals 1 073 741 824 bytes xiv 1 Introduction This chapter presents information about the architecture uses and features of the Oracle Database Gateway for DB2 400 It contains the following sections Introduction on page 1 1 10g Release 2 Gateways on page 1 2 a Two Phase Commit and Multisite Transactions on page 1 6 a Site Autonomy on page 1 6 Migration and Coexistence on page 1 6 a Security on page 1 6 Gateway Architecture on page 1 7 a How the Gateway Works on page 1 7 1 1 Introduction In today s global economy information is a company s most valuable resource Whether you need to analyze new markets tailor your products
70. P threshold cias eis 1 1 1000 SAME Additional number of TCP IP jobs 2 0 999 SAME Maximum number of TCP IP jobs NOMAX SAME NOMAX Start TCP IP listener YES SAME YES NO gt Fl Help F4 Prompt F9 Retrieve F12 Cancel For information about what values you should enter for these parameters move the cursor to the value on the panel and press PF1 The new values do not take effect until you shut down and restart the gateway that is specified in the Existing instance name parameter of the command 6 1 5 CHGORATUN Change Initialization Parameters On the OS 400 command line use the CHGORATUN command or use the GO CMDORAGTW command and specify option 4 Then press Enter The panel in Example 6 6 Change Oracle Gateway Initialization Parameters first page of panel 6 6 Oracle Database Gateway for DB2 400 Installation and User s Guide Gateway Commands appears CHGORATUN uses two panels Refer to Example 6 6 Change Oracle Gateway Initialization Parameters first page of panel and Example 6 7 Change Oracle Gateway Initialization Parameters second page of panel Note CHGORATUN can be run only while you are signed on as the user profile corresponding to the instance ID or as a user profile that has SECADM and ALLOBJ special authorities QSECOFR as distributed by IBM has such authorities For information about what values you should enter for these parameter
71. PH OBJTYPE PGM USER PUBLIC AUT USE You can use the EDTOBJAUT command to check the authorities of the three objects For example to check the current authority of the QWTSETP program in the QSYS library use the following command EDTOBJAUT OBJ QSYS QWTSETP OBJTYPE PGM If the PUBLIC line shows USE then no change is needed 4 4 1 Step 1 Mount the CD ROM Insert the CD ROM into the AS 400 CD ROM drive 4 4 2 Step 2 Start the Installation Process Enter the following command LODRUN drive where drive is the name of the CD ROM drive where you mounted the product CD ROM The panel in Example 4 1 Install Oracle Database Gateway Panel is displayed 4 4 3 Step 3 Set Required Parameters The following parameter must beset Instance Name 4 4 3 1 Panel Install Oracle Database Gateway 4 4 3 2 Action You must fill in the Instance name Use the default name of ORACLE or enter a name from one to six characters long The installation procedure either creates a new library uses an existing but empty library or uses an existing library that has been created using the DB2 400 SOL command CREATE COLLECTION If the library does not fit into this scenario then it will not be used Refer to Example 4 1 Install Oracle Database Gateway Panel The library must contain no objects other than those objects that were created by the CREATE COLLECTION command The prefer
72. S 400 ensure that the file is currently being journaled If the file is not journaled then the following messages are displayed ORA 28500 Connection from Oracle to non Oracle system returned this message SQL7008 EMP in CORPDATA not valid for operation ORA 02063 preceding 2 lines from AS400 7 4 1 Viewing Jobs Use the WRKACTJOB command to view all jobs Use the WRKACTJOB SBS myinst command to see only the jobs supporting your gateway instance substitute your gateway instance name for myinst If the gateway subsystem is active then you will see the LISTENER job in SELW select wait status on the WRKACTJOB panel under the gateway subsystem When the listener attempts to hand off a new connection to a pre started job its status is CPCW wait for completion of CPI communications call From the WRKACTJOB panel press PF14 to see all of the RUNORAGT jobs Two RUNORAGTTP jobs are usually present both in PSRW pre start or ready to run status 7 A Oracle Database Gateway for DB2 400 Installation and User s Guide Accessing OS 400 File Members If an active gateway connection exists then the RUNORAGT jobs can be in either RUN or TIMW time wait status 7 5 Accessing 0S 400 File Members An OS 400 physical file can have multiple members However OS 400 does not allow you to directly access a physical file member through SQL For example if you have an OS 400 filenamed EMP inthelibrary
73. STAMP columns as character strings and converts portions or subsets of the string to perform numerical operations TIME and TIMESTAMP values can be sent to DB2 400 as character literals or bind variables of the appropriate length and format Oracle DATE data types and IBM DATE data types are mapped to each other If an IBM DATE is queried then it is converted to an Oracle DATE with a zero midnight time of day If an Oracle DATE is processed against an IBM DATE column then the date value is converted to the IBM DATE format and any time value is discarded DB2 400 columns of DATE or TIME data type are assumed to be in ISO format For DATE the column format is yyyy mm dd and for TIME columns the format is hh mm ss TIME is in a 24 hour format Character representations of dates are different in Oracle format and DB2 400 format When an Oracle application SQL statement contains a date literal or conveys a date through a character bind variable the gateway must convert the date to a DB2 400 compatible format The gateway does not automatically recognize when a character value is going to be processed against an IBM DATE column Applications are required to distinguish character date values by enclosing them with the Oracle TO DATE function notation For example if EMP is a synonym or view that is accessing data in DB2 400 then instead of using the follow
74. Server Data can be copied from the Oracle Database 10g server to the DB2 400 server by two methods Triggers a SQL Plus COPY command 7 8 1 Triggers When updates are made to the Oracle Database synchronous copies of Oracle data and non Oracle data can be maintained automatically by using Oracle Database triggers For example suppose that you have an Oracle table ORA EMP that contains fields ENAME and EMPNO Suppose that you also have a table called DB2_EMP which is a copy of ORA EMP and which resides on DB2 400 You want all changes made to the Oracle ENAME field to be reflected immediately in your DB2_EMP table on DB2 400 In the following scenario an Oracle database trigger can be developed to run every time an update is made to the ENAME field in your Oracle ORA EMP table CREATE OR REPLACE trigger EMP TRIGGER after update of ename on SCOTT ORA EMP for each row BEGIN UPDATE SCOTT DB2_EMP tg4db2 SET ENAME NEW ENAME WHERE EMPNO NEW EMPNO END 7 8 Oracle Database Gateway for DB2 400 Installation and User s Guide Copying Data to Oracle Server from DB2 400 Server where tg4db2 isthe name of the database link that is used to access the gateway 7 8 2 SQL Plus COPY Command The SQL Plus COPY command copies data from the Oracle Database to the DB2 400 server The SOL command INSERT is not supported as a way to copy tables from the Oracle server to the ga
75. Support Oracle database no longer supports NCHAR or NVARCHAR2 data types With Oracle Database 10g server the Oracle Database Gateway for DB2 400 supports double byte data types GRAPHIC and VARGRAPHIC in DB2 400 terms in a manner similar to the manner in which they were supported in version 4 of the Oracle Database Gateway for DB2 400 That is to say in Oracle tools and applications they are supported as CHAR and VARCHAR2 data types of the appropriate size To support DB2 400 columns of GRAPHIC or VARGRAPHIC type you must set NLS LANG to a language in which the character set part corresponds to the CCSID of the VAR GRAPHIC column This way if the columnis CCSID 933 then the character set part of NLS LANG must be set to KO16DBCS refer to Appendix B Globalization Support In addition you must specify on the CHGORATUN screen that you want V4 graphics compatibility and the gateway job should be running in the CCSID of the GRAPHICS columns The job CCSID is also set on the CHGORATUN panel 9 8 2 For DBCS Users The Oracle Database Gateway for DB2 400 allows you to use DBCS Double Byte Character Set data under limited conditions which include a risk for data integrity 9 8 2 1 Double Byte Character Support This version of the Oracle Database Gateway for DB2 400 allows you to access and change columns in DB2 400 that are designated as VARGRAPHIC or GRAPHIC Because of the IBM definition of a VAR GRAPHIC c
76. TAB COMMENTS Column Name Description CHAR COL DECL LENGTH N A GLOBAL STATS N A USER STATS N A AVG COL LEN N A CHAR LENGTH N A CHAR USED N A V80 FMT IMAGE N A DATA UPGRADED N A HISTOGRAM N A C 27 USER TAB COMMENTS Comments on the tables and views that are owned by the user Column Name Description TABLE NAME Name of the object TABLE TYPE Type of object COMMENTS Comments on the object C 28 USER TABLES Description of the user s own tables Column Name Description TABLE NAME Name of the table TABLESPACE NAME Name of the table space containing the table CLUSTER NAME N A IOT NAME N A PCT FREE N A PCT USED N A INI TRANS N A MAX TRANS N A INITIAL EXTENT N A NEXT EXTENT N A MIN EXTENTS N A MAX EXTENTS N A PCT INCREASE N A FREELISTS N A FREELIST GROUPS N A LOGGING N A C 16 Oracle Database Gateway for DB2 400 Installation and User s Guide Column Name Description BACKED UP N A NUM ROWS Number of rows in the table BLOCKS N A EMPTY BLOCKS N A AVG SPACE N A CHAIN CNT N A AVG ROW LEN Average length of a row in the table in bytes AVG SPACE FREELIST
77. Text Telephone TTY access to Oracle Support Services within the United States of America 24 hours a day seven days a week For TTY support call 800 446 2398 Product Name The complete name for this product is Oracle Database Gateway for DB2 400 also abbreviated DG4DB2 400 in this document Platform Name In this manual the platform is called OS 400 The terms AS 400 and OS 400 both refer to the IBM iSeries computers and operating system which are referred to by IBM as IBM eServer iSeries servers and include the IBM i5 Conventions Examples of input and output to the system are shown in a special font WRKCFGSTS DEV device name Punctuation such as commas and quotes must be entered as shown unless otherwise specified All output is shown as it actually appears For input the following conventions apply UPPERCASE Uppercase words such as WRKCFGSTS indicate that a word or phrase must be entered exactly as spelled Italic code font Lowercase or uppercase entries in Italic font such as device name indicate that a word or phrase must be substituted such as the actual device name Angle brackets indicate that the enclosed arguments are required and that at least one of the arguments must be entered Do not enter the brackets themselves Square brackets indicate that the enclosed arguments are optional Do not enter the brackets themselves Vertical lines separate choices Ellipses indicate that the prec
78. _IMAGE DATA_UPG RADED HISTOGRAM Second highest value for tables with more than three rows These statistics are expressed in hexadecimal notation for the internal representation of the first 32 bytes of the values N A N A N A N A N A N A N A N A N A N A N A N A N A N A N A C 10 ALL TAB COMMENTS Comments on tables and views that are accessible to the user Column Name Description OWNER TABLE NAMF TABLE TYPE COMMENTS Owner of the object Name of the object Type of object Comments on the object C 11 ALL TABLES Description of tables that are accessible to the user Column Name Description OWNER Owner of the table TABLE NAME Name of the table TABLESPACE NAME CLUSTI Ej Ej IOT NAM PCT FREE R NAME Name of the table space containing the table N A N A N A Data Dictionary Views C 7 ALL TABLES Column Name Description PCT USED N A INI TRANS N A MAX TRANS N A INITIAL EXTENT N A NEXT EXTENT N A MIN EXTENTS N A MAX EXTENTS N A PCT INCREASE N A FREELISTS N A FREELIST GROUPS N A LOGGING N A BACKED UP N A NUM ROWS Number of rows in the table BLOCKS N A EMPTY BLOCKS N A AVG SPACE N A CHAIN CNT N A AVG ROW LEN Average length of a row in the table in bytes I AVG SPACE FREELIST BLOCKS N A
79. abase 10g server Oracle9i server or Oracle8i server release 8 1 7 or later 3 2 Oracle Database Gateway for DB2 400 Installation and Users Guide 4 Installing the Gateway Before you begin installation ensure that you have the required hardware and software described in Chapter 3 System Requirements This chapter contains the following sections Gateway Coexistence on page 4 1 Checklists on page 4 1 Preinstallation Steps on page 4 6 Installation Steps on page 4 6 Postinstallation Steps on page 4 6 4 1 Gateway Coexistence Oracle Database Gateway for DB2 400 Release 10 2 0 1 0 can coexist with any previous version of the product There are no known conflicts Oracle Corporation does not support upgrading a previous version gateway to a version 10 gateway You must reinstall the gateway The various instances of the gateway on a computer have no knowledge of each other other than through the DB2 400 files against which the gateways are executed and through the data dictionary tables 4 2 Checklists Caution Potential Loss of Data After the data dictionary tables are installed by a release 10 2 Gateway do not reinstall the data dictionary tables from an earlier release of the gateway If this is done by mistake then you can reinstall the version 10 2 data dictionary table by using the CRTORADDB command that is described in Chapter 6 Configuring the Gateway The following sections list the
80. an extension of Oracle stored functions An Oracle stored function is a schema object that logically groups a set of SOL and other PL SQL programming language statements together to perform a specific task and to return a value to the invoker of that function Applications may use standard Oracle PL SQL to invoke a stored function an Oracle stored function may also be invoked as part of an Oracle SELECT INSERT DELETE or UPDATE statement Similar to Oracle stored procedures an Oracle stored function can be located in a local instance of Oracle and in a remote instance Figure 9 6 shows two stored functions orafuncl is a function stored in the ORA1 Oracle instance while orafunc2 isa procedure stored in the ORA2 Oracle instance Figure 9 6 Calling Oracle Stored Function in a Distributed Oracle Environment orafunc1 Oracle Application Oracle Stored Function Oracle Database isin ORA1 orafunc2 Ba J Oracle Stored Function ORA2 If the application needs to maintain location transparency then a synonym can be created CREATE SYNONYM ORAFUNC2 FOR orafunc2tora2 where orafunc2 is the function that is stored in ORA2 After this synonym is created the application no longer needs to use the database link specification for invoking the stored function at the remote Oracle Database In Figure 9 6 the reference to function orafunc2 is us
81. and Client Configuration on page B 4 Message Availability on page B 5 DB2 400 GRAPHIC Support on page B 5 B 1 Overview of Globalization Support Globalization Support is a technology that enables Oracle applications to interact with users in their native language using the conventions of that language for displaying data The Oracle Globalization Support architecture is datadriven enabling support for specific languages and character encoding schemes to be added without requiring any changes in source code There are a number of settings in the gateway DB2 400 Oracle server and the client that affect Globalization Support processing In order for translations to take place correctly character settings of these components must be compatible Each character in one encoding scheme must have a matching character in another encoding scheme B 2 Gateway Configuration After the gateway is installed you must use the CHGORATUN command if you need to change language settings B 2 1 CHGORATUN Language Specification The CHGORATUN command allows you to change the language parameter that defines the character set that is used for the gateway The language parameter that is entered with this command specifies the conventions such as language used for gateway messages names of days and months symbols for AD BC AM and PM and the default language sorting mechanism The syntax for specifying the language parameter is Globalization Su
82. annot write to more than one DB2 400 database A stored procedure call through DB2 400 counts as a write A call to a user defined function within a SELECT UPDATE DELETE or INSERT does not count as a write Single SOL statements using a JOIN can refer to tables in multiple Oracle databases multiple DB2 400 databases or both 9 1 1 Connecting to DB2 400 Databases with Database Links A connection to the gateway is established through a database link when that link is first used in an Oracle database session The connection remains active until the session ends Another session or user can access the same database link but will get a separate connection to DB2 400 Connections to DB2 400 might be limited by factors such as memory gateway parameters or OS 400 server resources 9 1 2 Read and Write Access to DB2 400 Databases You can perform read and write functions to a DB2 400 database SELECT INSERT UPDATE and DELETE are all valid operations A single transaction can use an UPDATE statement to change multiple Oracle databases Only one DB2 400 database can be updated within a transaction If a stored procedure call is directed to DB2 400 then no updates or stored procedure calls can be directed to any other DB2 400 databases during the transaction Note that a user defined function is treated differently as Oracle assumes a user defined function does not write to DB2 400 A single transaction
83. application for example SQL Plus and the Oracle integrating server when using UPDATE DELETE and INSERT statements in that it allows one UPDATE DELETE or INSERT statement to be used with a series of values For more information about array processing usage and implementation in the Oracle application refer to Oracle Database SQL Reference Figure 9 1 Use of Array Size Definition in the DB2 400 Architecture for UPDATE DELETE and INSERT Oracle Database AS 400 Client Oracle Oracle AAA a aera cial 1 Application Application defines array defines array Database DB2 400 EAS i Gateway Database j Oracle Net Oracle Net Note For performance reasons Oracle recommends setting the initial Oracle application array size between 10 and 100 9 2 1 Fetch Reblocking The array size of the application for SELECT is effective between the application and the Oracle Database 10g server However the block size of the buffer between the Oracle integrating server and the gateway is governed by two initialization parameters RPC FETCH SIZEand RPC FETCH REBLOCKING These gateway initialization parameters correspond to the Heterogeneous Services initialization parameters HS_RPC_FETCH_SIZE and HS_RPC_FETCH_REBLOCKING respectively Figure 9 2 illustrates this Refer to Oracle Database Administrator s Guide for more information
84. are treated as if they were in the character set ID in which the gateway runs When using the CHGORATUN command the User Profile CCSID line specifies the character set ID in which the gateway runs The ORARAW data area is used to hold the data conversion specification 6 2 3 Read Only Gateway The gateway can be configured with read only capabilities The read only option may provide improved performance and security based on your configuration and parameter selections The READONLY data area controls whether the gateway is enabled in this mode The default setting for the read only feature is NO You can change the value of this environment parameter using the CHGORATUN command If you enable the read only feature by changing the setting to YES then only queries SELECT statements are allowed to DB2 400 The capabilities which control whether updates are allowed through the gateway are not enabled These capabilities include insert update delete and stored procedure support pass through SOL DB2 400 stored procedures but not DB2 400 User Defined Functions Statements attempting to modify records at the gateway site are rejected Oracle recommends that you do not routinely switch between settings of the read only parameter If you need both update and read only functionality then you should install two separate instances of the gateway with different read only settings Refer to CHGORATUN Change Initialization
85. arting and Stopping the Gateway ococccicinonicnonorananononananananonononanonononononanononon oran nennen 8 3 Working with Oracle Gateway Jobs sssessseeeeee eee een nennen 8 3 Starting and Stopping the Gateway Using the Command Line 8 4 Starting the TCP IP Listener e eet ettet het 8 4 Displaying the Gateway Call Stack sse enne nenne 8 4 Working with Gateway Job Locks eee nennen nennen 8 4 Displaying Oper Files ettet terere dees de e etn eee e ette tes 8 4 Gateway Securlty caen core petite dre Na i e qe ror OA SI dee iL EIE 8 4 Streams Replication uii unte n ette ene itio te er te ine ia indie 8 5 Developing Applications 9 1 9 1 1 9 1 2 9 1 3 9 2 9 2 1 9 3 9 4 9 4 1 9 4 2 9 5 9 6 9 6 1 Gateway Appearance to Application Programs c cccccscssssesesteseesceseseseecenensseseeeeesesesnanenens 9 1 Connecting to DB2 400 Databases with Database Links sse 9 2 Read and Write Access to DB2 400 Databases essen 9 2 Accessing Tables in Multiple Databases sss 9 2 Array Processi E nisus tini ese iine ed vd NEP vire SO qr tid ce ge EPA ug ie 9 2 Fetch Reblocking 3 eoo ee es ee titre tron 9 3 Using Oracle Stored Procedures with the Gateway sess 9 4 Using DB2 400 Stored Procedures with the Gateway sss 9 5 Executing DB2 400 Stored Procedures from Applications sss 9 6
86. as in the following example SELECT from user table gateway 7 2 Oracle Database Gateway for DB2 400 Installation and Users Guide Using the Synonym Feature 7 1 5 Dropping Database Links You can drop a database link with the DROP DATABASE LINK statement For example to drop the public database link named dblink enter the following statement DROP PUBLIC DATABASE LINK dblink Do not drop a database link if it might be required to resolve an in doubt distributed transaction Refer to the Oracle Database Administrator s Guide for additional information about dropping database links 7 1 6 Examining Available Database Links The data dictionary of each Oracle Database stores the definitions of all the database links in that database The USER DB LINKS data dictionary view shows the database links that are defined for a specific Oracle Database user The ALL DB LINKS data dictionary views show all defined database links both public and private The user has access to all these views The DBA DB LINKS dictionary view which is accessible only to users with DBA authorization shows all database links that are defined in the Oracle Database instance 7 1 7 Limiting the Number of Active Database Links You can limit the number of connections from a user process to remote databases with the INIT ORA parameter OPEN LINKS This parameter controls the number of remote connections a
87. at any strings to be INSERTed into VAR GRAPHIC columns are in their wide form on the ASCII computer The 9 16 Oracle Database Gateway for DB2 400 Installation and User s Guide Converting DB2 400 Data Types to Oracle Data Types string cannot contain any single byte characters This means that when converting from the ASCII based string to a DBCS string the conversion does not result in a DBCS string with a SHIFT OUT or SHIFT IN character at any other position than the first character position for the SHIFT OUT character or the last character position for the SHIFT IN character The same is true for values that are represented by constants or for values that are represented by bind variables 2 Specifying the FORCE SB option causes all single byte characters to be translated to their corresponding double byte values on input through either an INSERT or a bind variable The entire string is then INSERTed into the VAR GRAPHIC column On output that is to say on a SELECT each VAR GRAPHIC column will be examined for double byte characters that correspond to single byte characters Each such character is replaced by its corresponding single byte character This is where the data integrity problem arises Because single byte characters have been forced to their corresponding double byte characters on input there is no way to know if a double byte character actually came from the translation of an
88. ata array processing 6 10 setting optional parameters array processing 6 10 buffer size ORA 28528 error buffer too small for LONG data type 2 6 C CCSID data conversion 6 11 Globalization Support considerations B 2 supported character sets B 2 supported languages B 3 supported territories B 3 CFGTCP command 5 10 changes to the system upon installation 1 8 CHAR format 9 19 character set CCSID data conversion 6 11 CHGORATUN command B 1 DB2 400 CCSID considerations B 2 DB2 400 GRAPHIC support setting NLS LANG 9 16 B 5 DBCS double byte character set 9 16 Globalization Support character settings must be compatible B 1 NLS LANG specifies a single byte character set 9 16 supported character sets B 2 GRAPHIC constants in SOL commands 2 4 server and client configuration B 4 SOL command to check character set of existing database B 4 CHARACTER SET parameter B 4 character strings converting data types 9 18 Index 1 processing 9 18 CHECKSUM command Advanced Security 1 4 CHGGTWDBG command command description 6 8 purpose 6 1 CHGORANET command assigning a port number 5 7 changing parameter values in ORA SOLNET 5 3 changing parameters A 2 purpose 6 1 trace files 5 3 CHGORAPJE command command description 6 6 prestarted jobs 6 4 purpose 6 1 CHGORATUN command changing default BLOCKSIZE 6 11 command description 6 6 data conversion 6 11 gateway configuration B 1 language specificati
89. atement such as SELECT INSERT DELETE or UPDATE then you must specify the referenced name in the IFS file HOME MYINST RDBMS ADMIN CALL NAMES ORA where MYINST is your instance name If you attempt to use an AS 400 stored function DB2 400 user defined function in a SOL command without specifying that name in the above IFS file then you will get the Oracle error ORA 06571 function xxxxx does not guarantee not to update database After doing the above the gateway is able to access the stored function using a standard PL SQL call syntax or via a reference in a SOL DML statement For example suppose our DB2 400 stored procedure gives us what should be a revised salary given a specific employee name The employee name JOHN SMYTHE is passed to the DB2 400 stored function REVISE SALARY FUNC The DB2 400 stored function retrieves the salary value from the DB2 400 database and calculates a new revised salary for our specific employee Since REVISE SALARY FUNC is a function it returns this salary value as the return value of the function The revised salary returned in RESULT is used to update the SAL column in the EMP table of the Oracle Database as follows DECLARE INPUT VARCHAR2 15 RESULT NUMBER 8 2 9 10 Oracle Database Gateway for DB2 400 Installation and User s Guide Using DB2 400 Stored Functions with the Gateway BEGIN INPUT JOHN SMYTHE RESULT MYLIB REVISE SALARY FUNCQGD
90. ateway 1 8 ALL CATALOG view description C 2 ALL COL COMMENTS view description C 2 ALL CONS COLUMNS view description C 2 ALL CONSTRAINTS view description C 3 ALL DB LINKS data dictionary view examining available database links 7 3 ALL IND COLUMNS view description C 3 ALL INDEXES view description C 4 ALL OBJECTS view description C 5 ALL SYNONYMS view description C 6 ALL TAB COLUMNS view description C 6 ALL TAB COMMENTS view description C 7 ALL TABLES view description C 7 ALL USERS view description C 9 ALL VIEWS view description C 9 ALTER SESSION CLOSE DATABASE LINK statement 7 2 ANO Advanced Networking Option gateway advantages 1 4 API applications program interface distributed processing 5 1 application development databaselink 9 2 application server support 1 4 applications SOL Plus 1 6 architecture gateway 1 7 array definition 9 2 processing array blocksize 6 10 tuning the gateway 9 2 ARRAY BLOCK SIZE parameter fetch reblocking 9 4 setting optional parameters 6 10 AS 400 database accessing file members 7 5 AUTOCREATE CONTROLLER parameter 5 6 background processes in gateway 1 7 Index bequeath mechanism TCP IP 5 6 binary literal notation restriction 2 4 bind variables Oracle bind variables become DB2 400 parameter markers 9 23 restriction binary literal notation 2 4 columns defined with RAW data 2 5 Oracle bind variables 2 5 BLOCKSIZE data area retrieving d
91. ateway can force those double width characters back to single width characters The ability to do this results in a data integrity exposure because the gateway cannot determine whether a given double width character from a DB2 400 column was INSERTed as a single width character that was forced to become a double width character or was entered as a true double width character 2 4 2 Owners of DB2 400 Components The following sections describe restrictions related to DB2 400 components 2 4 2 1 DD Basic Tables and Views The owner of Data Dictionary DD basic tables and views is OTGDB2 This cannot be changed 2 4 2 2 Binary Literal Notation Oracle SQL uses hexadecimal digits surrounded by single quotation marks to express literal values being compared or inserted into columns defined as RAW Currently this is not converted to DB2 400 syntax an X followed by quoted hexadecimal digits when the SQL destination is the gateway You must use bind variables to compare or insert into a DB2 400 server column that is defined with the FOR BIT DATA option 2 4 2 3 Programmatic Limitations Gateway design requires that all host variables in a SOL operation be bound before a describe function is performed This is required because of a limitation in the Oracle Call Interface OCI which requires that all OCI bind calls for a given statement must be completed before an OCI describe call is made 2 4 Oracle Database Gatew
92. ay for DB2 400 Installation and Users Guide Known Restrictions 2 4 2 4 Columns Defined with RAW Data When you select RAW data into character bind variables the CHAR column must be two times the size of the RAW data Selecting RAW data into character bind variables causes implicit RAW to HEX conversion If the character bind variable column is too small then the SELECT statement fails 2 4 2 5 GLOBAL NAMES Initialization Parameter If GLOBAL NAMES is set to TRUE in the Oracle integrating server INIT ORA file then to be able to connect to the gateway you must specify the value of the Heterogeneous Services HS initialization parameter H5 DB DOMAIN in the Change Oracle Gateway Initialization Parameters panel to exactly match the value of the Oracle server DB DOMAIN parameter Refer to Chapter 6 Configuring the Gateway for more information 2 4 2 6 Precompiler Limitations The SQLCHECK option must be set to NONE when precompiling programs with Oracle Precompilers 2 4 2 7 Some SQL Functions Post processed The Oracle Database 10g server provides more functionality for some SQL functions compared to similarly named DB2 400 SQL functions As a result these SOL functions are not passed through from the Oracle Database 10g server to DB2 400 They are instead post processed SUBSTR is an example of such a function because the semantics of the SUBSTR function in Oracle as compared to that i
93. b2 capture END For detailed information about Oracle streams replication refer to Oracle Streams Concepts and Administration 8 8 Oracle Database Gateway for DB2 400 Installation and User s Guide 9 Developing Applications An application that is written to access information in an Oracle DB can be made to access similar information in DB2 400 by simply qualifying the table view stored procedure or function name with a database link Using a database link the access can be made transparent by using synonyms or views of the DB2 400 tables However fundamental SQL data type and semantic differences exist between the Oracle server and DB2 400 Read this chapter to learn these differences and to find information about developing applications To develop applications that access OS 400 data through the gateway you need to understand the topics in this chapter The following sections are included Gateway Appearance to Application Programs on page 9 1 Array Processing on page 9 2 Using Oracle Stored Procedures with the Gateway on page 9 4 Using DB2 400 Stored Procedures with the Gateway on page 9 5 Passing DB2 400 SOL Statements Through the Gateway on page 9 12 Converting DB2 400 Data Types to Oracle Data Types on page 9 14 SOL Functions on page 9 20 Oracle Server SOL Construct Processing on page 9 21 Oracle Server and DB2 400 Differences on page 9 22 Oracle Data Dictionary Emulation in a DB2 400 Server on page 9 23 O
94. base SQL Reference Figure 9 2 Array Size Definition in the DB2 400 Architecture for SELECT Oracle Database Oracle e RPC FETCH SIZE Client MOI E Application defines the array j 1 defines array size in bytes Database DB2 400 Gateway Database Oracle Net Oracle Net 1 l AS 400 G Gateways ARRAY BLOCK SIZE 9 3 Using Oracle Stored Procedures with the Gateway The gateway stored procedure support is an extension of Oracle stored procedures An Oracle stored procedure is a schema object that lo gically groups a set of SOL and other PL SQL programming language statements together to perform a specific task Oracle stored procedures are stored in the database for continual use Applications use standard Oracle PL SQL to invoke stored proced invoke a stored procedure Oracle stored procedures can be located in a local ures There are no other ways to instance of Oracle and a remote instance Figure 9 3 shows two stored procedures oraprocl1 is a procedure stored inthe ORA1 Oracle instance while oraproc2 Oracle instance Figure 9 3 Calling Oracle Stored Procedures in a is a procedure stored in the ORA2 Distributed Oracle Environment oraproc1 Oracle Application Oracle Stored Procedure C rib SN EE 8 Oracle Database m d 3 ORA1 qiie WE gt Woa
95. can use a SELECT statement to retrieve data from multiple Oracle databases and multiple DB2 400 databases The gateway is set up with commitment control set to CHG Refer to the IBM reference for IBM DB2 for OS 400 DB2 400 SQL for more information regarding OS 400 commitment control 9 1 3 Accessing Tables in Multiple Databases Single SOL statements using JOINs can refer to tables in multiple Oracle Databases or multiple DB2 400 databases 9 2 Array Processing When evaluating and tuning your gateway configuration you may achieve performance gains by using the Oracle Array Processing interface An array is a 9 2 Oracle Database Gateway for DB2 400 Installation and User s Guide Array Processing collection of data items called elements that are associated with a single variable With arrays you can use a single SOL statement to manipulate an entire collection of data items For example suppose you want to insert information regarding 100 employees into the EMP table on DB2 400 Without arrays your program must do 100 individual INSERTs one for each employee With arrays only one INSERT is necessary The use of array processing reduces network calls which may save elapsed time and CPU cycles In addition when using INSERT for multiple rows DB2 400 processing is optimized by retaining the original SOL statement for repeated execution The array size is a factor in transferring data between the client
96. capabilities Oracle Net also allows applications to connect to multiple Oracle servers or gateways across a network selecting from a variety of communications protocols and application program interfaces APIs to establish a distributed processing and distributed database environment A communications protocol is a set of implemented standards or rules governing data transmission across a network An API is a set of subroutines providing a programming interface for application processes to the network environment 5 1 1 Distributed Processing Dividing processing between a front end computer that is running an application and a back end computer that is used by the application is known as distributed processing Oracle Net enables an Oracle tool or application to connect to a remote computer containing an Oracle server or Oracle gateway Oracle Net 5 1 5 1 2 Distributed Database Several databases that are linked through a network and that appear as a single logical database are known as a distributed database An Oracle tool running on a client computer or on an Oracle server running on a host computer can share and obtain information that is retrieved from other remote Oracle servers or Oracle gateways Regardless of the number of database information sources you might be aware of only one logical database 5 1 3 Terminology for Oracle Net The following terms are used to explain the architecture of Oracle Net for AS 400 host i
97. cations 9 1 diagnosing errors 10 2 dictionary mapping 1 4 DICTIONARY view description C 10 distributed database Oracle Net 5 2 processing Oracle Net 5 1 queries 7 6 documentation errors 10 3 requirements 3 2 domain name configuring TCP IP defining host name 5 7 double byte character support 9 16 DROP DATABASE LINK statement example 7 3 DSPLIND command displaying line description parameters 5 6 DSPPGM command verifying gateway installation and version 4 7 DUAL view description C 10 E EDTOBJAUT command to check authority 4 3 EMPDB2 synonym using the synonym feature 7 3 ENDSBS command authorization to stop a gateway 8 3 stopping a gateway 8 4 error ORA 1017 invalid user ID or password caution to synchronize recovery password 6 10 ORA 12154 connection error resolving connection problems 5 10 ORA 2068 connection error resolving connection problems 5 9 ORA 28500 file not journaled 4 8 resolving connection problems 5 10 ORA 28509 connection error resolving connection problems 5 9 ORA 28511 connection error resolving connection problems 5 9 ORA 28528 example buffer too small for LONG datatype 2 6 ORA 3114 connection error resolving connection problems 5 9 SOLO0105 Mixed or Graphic String Constant Not Valid 9 16 errors diagnosing via categories 10 3 mapping DB2 400 messages to Oracle 10 1 messages diagnosis and reporting 10 1 reporting documentation 10 3 external
98. cessecescecssesessessscessecessesscessnecessecesesensecenes 10 3 10 3 2 Incorrect OUtpu L a a ERE UH EE M NE enna ees 10 3 10 3 3 Oracle External Errors 5 a E atte ae 10 4 10 3 4 Performance A ed ert ete 10 4 10 3 5 Missing Funcional tirer enr rte eite e re e rte divs dee rente 10 4 Oracle Net Files and Members A 1 File Merbets iui ace ier e vel eere cree eder eve Eee tee d ee tet dee cit eee Eee dde A 1 A 1 1 LOG File Members irradia A 1 A 1 2 ORA File Members sionista A 2 A 1 2 1 EISTENER comimos lato ee te Ee eee e Elec i GI dida A 2 A 1 2 2 LISTENER CHG 5 nds et eem te tec He ete A 2 A 1 2 3 A 1 2 4 A 1 2 5 A 1 2 6 A 1 3 A 1 4 A 2 TLISTENLOG Kes iciatis iced ln en A 2 SOENE TV nne AeA Sekine del e GR do i A 2 SOLENET GLEIG re etki ette Set hee ee eee eee eod A 2 Displaying File Members incidido roter etit heiter ie eas A 2 TRE Hile Members ee exe E en Ee ice Hb vectus A 2 ORA files in the Integrated File System IPS sse A 3 Agent Trace Files desee pee eue Ata els Ait de theta deer ibt e tests uias A 3 Globalization Support B 1 B 2 B 2 1 B 3 B 3 1 B 3 2 B 4 B 5 B 6 B 7 Overview of Globalization Support sse eene nennen nnns B 1 Gateway Configuration eerie ert toe A E raan FAES E aE B 1 CHGORATUN Language Specification sss eee eene B 1 DB2 400 Coded Character Set Considerations sse B 2 Supported Character Sets i setetessieteetie tm te
99. curity 6 11 recovery process 7 7 user and password 6 4 release information 2 1 remote transaction program 3 1 replication heterogeneous environment 7 8 requirements documentation 3 2 hardware 3 1 software 3 2 software journaling 4 6 result sets retrieving with passthrough 9 12 retrieving multiple rows array processing 9 2 using BLOCKSIZE 6 10 ROUTINE_NAME column executing DB2 400 stored procedures 9 6 ROUTINE_SCHEMA column executing DB2 400 stored procedures 9 6 routines PL SQL 1 5 RPC FETCH REBLOCKING parameter YES is default value 9 3 entering on Change Oracle Gateway Initialization Parameters panel 6 7 initialization parameter notes 6 7 may be changed by CHGORATUN command 9 3 RPC FETCH SIZE parameter changing initialization parameters 6 7 entering on Change Oracle Gateway Initialization Parameters panel 9 3 running gateway commands 6 2 RUNORALSN program runs the TCP IP listener A 2 RUNSQLSTM command required for cloning an instance 3 2 S SBMJOB command OSECOFR ID 4 9 removing IBM observability 4 9 SECADM special authority 4 2 security advanced 1 4 ANO 1 4 encryption 1 4 on the gateway 8 4 SELECT FOR UPDATE clause Oracle server cannot post process 9 22 SELECT statement array size in fetch reblocking 9 3 read and write access to DB2 400 databases 9 2 SELECT without the FOR UPDATE clause 9 22 SQL differences 1 9 session connection 7 1 SID names specifying 5 4
100. d how is it reproduced 10 3 3 Oracle External Error Oracle error messages are produced whenever an Oracle gateway server tool or DB2 400 system detects an error condition Depending on the circumstances error messages might be fatal or nonfatal to the utility or server Be prepared to identify the exact error message and message number that are received and the complete circumstances surrounding the error 10 3 4 Performance AS 400 system performance is determined by many factors most of which are not within the control of Oracle Considerations such as system load I O topology network topology use and DB2 400 resource availability and use make the documentation of performance errors difficult Provide detailed information about the state of your environment when reporting an error Specific documentation might include CPU type and memory configuration database topology I O topology network topology system workload by type Oracle workload characterization query execution plans a DB2 400 resource information 10 3 5 Missing Functionality Enhancement requests can be opened with Oracle Support Services to request the inclusion of functions and features that are not currently available with Oracle products When opening an enhancement request describe the specific feature or function to be added to the product and provide a business case to justify the enhancement 10 4 Oracle Database Gatewa
101. d to be representable in the Oracle column Problems like this occur when the CHARACTER SET of the Oracle DB is something like UTF8 In this case no matter what CCSID the DB2 400 column has there is going to be a character set expansion This is because there are at least 192 actual code points in an SBCS code page on the AS 400 but there are only 94 usable codepoints when that character is to be represented as a single byte UTFS character Here is an example The Japanese Yen character appears at codepoint 0xB2 in the code page represent by CCSID 37 which corresponds to Oracle Characterset WE8EBCDIC37 But the codepoint for this character is OxC2A5 in UTF8 The cent sign appears at codepoint Ox4A in the code page represented by CCSID 37 but is represented by the codepoint 0xC2A2 in UTF8 So even though your data in DB2 400 does not contain either of a Yen or Cent Sign symbol the general case must hold that to represent either of these single byte SBCS characters in the Oracle DB one needs two bytes Now it actually turns out that there is one and only one character in the CCSID 37 codepage that cannot be represented by ANY sequence in the UTF8 characterset That SBCS character is OxFF and that is represented by the UTF8 sequence OxEFBFBD So if one does a CREATE TABLE x AS SELECT then one is always going to get at least a triple expansion of the columns That is a column represented by a CHAR 5 in DB2 400 and where that colum
102. e environment The manner in which the gateways are integrated with the Oracle Database 10g server ensures that the latest features of each database release are always available immediately to the gateway Distributed capabilities Heterogeneous data can be integrated seamlessly because Oracle Database distributed capabilities such as JOIN and UNION can be applied against non Oracle data without any special programming or mapping Distributed query optimization The Oracle Database 10g server can use its advanced query optimization techniques to ensure that SQL statements are executed efficiently against any of your data The data distribution and storage characteristics of local and remote data are considered equally Two phase commit protection The Oracle two phase commit mechanism provides consistency across data stores by ensuring that a transaction that spans data stores is still treated as a single unit of work Changes are not committed nor permanently stored in any data store unless the changes can be committed in all data stores that are affected Stored procedures and database triggers The same Oracle stored procedures and database triggers can be used to access all of your data ensuring uniform enforcement of your business rules across the enterprise Introduction 1 3 1 2 SQL 1 2 1 3 Extended Advanced Networking Internet and Intranet Support The gateway integration with the Oracle Database 10g server
103. e 6 11 for more information 1 4 Site Autonomy All Oracle Database 10g server products including gateways supply site autonomy For example administration of a data source remains the responsibility of the original system administrator Site autonomy also functions so that gateway products do not override the security methods of the data source or of the operating environment 1 5 Migration and Coexistence The integration of a data source through the gateway requires no changes to be made to applications at the data source As a result of this the Oracle Database 10g server technology is nonintrusive providing coexistence and an easy migration path 1 6 Security The gateway does not bypass existing security mechanisms Gateway security coexists with the security mechanisms that are already used in the operating environment of the data source Functionally gateway security is identical to that of an Oracle Database 10g server as described in the Oracle Database Administrator s Guide Oracle Database security is mapped to the data dictionary of the data source 1 6 Oracle Database Gateway for DB2 400 Installation and User s Guide How the Gateway Works 1 7 Gateway Architecture The gateway architecture consists of four main components as shown in Figure 1 1 1 Client The client is an Oracle application or tool 2 Oracle Database 10g server The Oracle Database 10g server is an Oracle instance 3 Oracle Database Gateway f
104. e resulting from the DSPPGM command informs you of the gateway version You can also find out what version of the gateway is running by looking in the job logs for a RUNORAGxxx job Usually the job name is RUNORAGTTP 4 5 3 Step 2 Verify Journaling Journal all AS 400 files that are changed through the gateway To start journaling for a file use the STRIRNPF command To use the STRIRNPF command you must have previously created a journal and a journal receiver as illustrated in the following example Note that if you installed the gateway into a library that was created as a SQL collection then the journal and journal receiver already exist You do not need to explicitly journal files that are created in a library that is part of a DB2 400 SOL collection You can journal many files to the same pair of journal and journal receiver CRTJRNRCV JRNRCV instance QSQJRNRCV CRTJRN JRN instance QSQJRN JRNRCV instance QSQURNRCV STRJRNPF FILE library file JRN instance QSQJRN where instance isthename that is given to the gateway when it is installed library isthe name of the library where the journal is located file isthe name of the file that you are journaling Installing the Gateway 4 7 Note If you attempt to change a file that is not journaled then you may receive an ORA 28500 error connection from ORACLE to a non Oracle system returned this message followed by a second error message that is generated by the target syst
105. e same as those from the SOL command in Example 1 and might look like the following 7 6 Oracle Database Gateway for DB2 400 Installation and User s Guide Performing Distributed Queries Table 7 1 Example Results from SQL Distributed Queries CUSTNAME PROJNO ENAME SPEND ABC Co 1 Jones 400 ABC Co 1 Smith 180 XYZ Inc 2 Jones 400 XYZ Inc 2 Smith 180 7 6 2 Two Phase Commit Processing For a database to fully participate in a two phase commit transaction it must support both a prepare phase and a commit phase for committing transactions The prepare phase ensures that all participating nodes that are referenced in a distributed transaction are prepared to commit or to abort the transaction regardless of intervening failures The Oracle database supports two phase commit transactions So any number of Oracle Database servers can participate in a distributed two phase commit transaction The prepare phase is performed when a COMMIT is issued at the end of a distributed transaction Note The prepare phase occurs automatically when an application COMMIT is executed No other action is necessary DB2 400 does not support a prepare phase for committing a transaction Consequently it does not support two phase commit transactions Therefore the two phase commit protocol is limited when the gateway participates in a distributed transaction In this case the gateway becomes the commi
106. e tables Column Name Description OWNER CONSTRAINT NAME CONSTRAINT TYPE TABLE NAMF SEARCH CONDITION R OWNER R CONSTRAINT NAME DELETE RULE STATUS DEFERRABLE DEFERRED VALIDATED GENERATED B RELY LAST CHANGI uM INDEX OWNER INDEX NAME INVALID VIEW RELATED Owner of the constraint definition Name associated with the constraint definition Type of constraint definition Name associated with the table with the constraint definition Text of the search condition for the table check Owner of the table used in the referential constraint Name of the unique constraint definition for the referenced table Delete rule for the referential constraint Status of the constraint N A N A N A N A N A N A N A N A N A N A N A C 5 ALL IND COLUMNS Columns of the indexes on the accessible tables Column Name Description INDEX OWNER INDEX NAME TABLE OWNER TABLE NAMFE COLUMN NAME COLUMN POSITION COLUMN LENGTH CHAR LENGTH DESCEND Owner of the index Name of the index Table owner Table name Column name Position of column within the index Indexed length of column N A ASC for Ascending key DESC for Descending key Data Dictionary Views C 3 ALL INDEXES C 6 A
107. e the ENDSBS command 8 3 1 Working with Oracle Gateway Jobs To work with gateway jobs specify option 5 next to the job This displays the OS 400 Work with Job menu to work with a specific gateway job Administering the Gateway 8 3 8 3 2 Starting and Stopping the Gateway Using the Command Line An instance of the Oracle Database Gateway for DB2 400 runs within an OS 400 subsystem All jobs relating to that instance run within the subsystem You use the STRSBS command to start a subsystem For example to start an instance of a gateway called ORACLE you enter the following command STRSBS ORACLE ORACLE where the first ORACLE is a library name and the second ORACLE is the name of a subsystem To stop a gateway you must end the subsystem by using the ENDSBS command For example to stop a gateway called ORACLE to immediately end all jobs within the ORACLE subsystem and to stop the subsystem itself you enter the following command ENDSBS ORACLE OPTION IMMED 8 4 Starting the TCP IP Listener Specify option 7 in the Opt field that is next to the gateway subsystem name if you want to start the gateway listener The gateway listener is identified by LIS in the TYPE field that is displayed on the main menu 8 5 Displaying the Gateway Call Stack Displaying the gateway call stack is not needed for most installations but might be requested by Oracle Support Services during problem isolation and determinatio
108. ed procedure is defined the attributes of each entry in the argument list for the stored procedure are placed in the QSYS2 SYSPARMS table The gateway uses this information to convert the data type of each argument to its corresponding PL SQL data type Refer to Converting DB2 400 Data Types to Oracle Data Types on page 9 14 for gateway data type conversion rules After the lookup of the description of the DB2 400 stored procedure is complete the gateway uses this information to execute the DB2 400 stored procedure 9 4 2 Considerations for Using Stored Procedures with DB2 400 Following is a list of special considerations for using the procedure feature and DB2 400 stored procedures with the Oracle Database Gateway for DB2 400 PL SQL records cannot be passed as parameters when invoking a DB2 400 stored procedure The gateway executes the DB2 400 CALL statement to execute DB2 400 stored procedures Commitment control of database changes that are made with the DB2 400 stored procedure is managed by DB2 400 For more information regarding commitment control refer to the IBM reference for DB2 400 The gateway supports both GENERAL and GENERAL WITH NULLS linkage conventions of DB2 400 stored procedures a The GENERAL linkage convention means that the parameters that are passed to and from DB2 400 stored procedures cannot be null The GENERAL WITH NULLS linkage convention means that parameters passed
109. ed to call that same function in the ORA2 instance In the same way Oracle can be used to access DB2 400 user defined functions through the gateway In Figure 9 7 empfunc is an Oracle stored function that subsequently accesses data in DB2 400 using the gateway 9 8 Oracle Database Gateway for DB2 400 Installation and User s Guide Using DB2 400 Stored Functions with the Gateway Figure 9 7 Using Oracle Stored Functions with DB2 400 Oracle empfunc Oracle Application A Stored Function HE aiit Oracle a dio Database m a yaa yee Bop s xmi E Oracle Database Gateway for DB2 400 DB2 400 As with the Oracle database standard PL SQL is used to create the function Standard PL SQL may be used to execute the function or the function may be executed with a reference in a SOL command such as SELECT UPDATE INSERT or DELETE There is no difference in the gateway case except that the stored function is accessing DB2 400 rather than the Oracle database Oracle assumes that the underlying user defined function on the DB2 400 does not change any data on the AS 400 side if the stored function is referenced in a SQL command One explicitly declares that a stored function does not change the AS 400 data by specifying that function name in an IFS file named HOME MYINST RDBMS ADMIN CALL NAMES ORA Where MYINST is your instance name If you attempt to u
110. eding item can be repeated You can enter an arbitrary number of similar items Commands reserved words and keywords appear in uppercase in both examples and text A file ID can appear with both uppercase and lowercase text When portions of a file ID appear in italic case this indicates that those portions in italic case can vary Reserved words and keywords must always be entered as is and have reserved meanings within Oracle applications Related Documents Oracle Books For more information refer to the following Oracle documentations a Oracle Database Advanced Security Administrator s Guide xii a Oracle C Call Interface Programmer s Guide a Oracle Call Interface Programmer s Guide a Oracle Database Advanced Replication a Oracle Database Application Developer s Guide Fundamentals a Oracle Database Application Developer s Guide Large Objects a Oracle Database Application Developer s Guide Object Relational Features a Oracle Database Backup and Recovery Advanced User s Guide a Oracle Database Backup and Recovery Basics a Oracle Database Administrator s Guide a Oracle Database Error Messages a Oracle Database Globalization Support Guide Oracle Database New Features Oracle Database Performance Tuning Guide a Oracle Database Reference a Oracle Database Security Guide Oracle Database SQL Quick Reference a Oracle Database SQL Reference a Oracle Database Heterogeneous Connectivity Administrator s Guide a
111. eet rennen reete 5 8 vi 5 4 5 Step 4 Verify that the Listener is Started sse 5 8 5 5 TCP IP Connection Problems seen nennen tren i e e s 5 8 5 6 Resolving Connection Problems eee Ai r i nennen 5 8 5 6 1 AS 400 Connection Problems eese eee nennen erre n tense nter eterne nte ne nena 5 8 5 6 1 1 The A5 400 is not reachable iiie eee etie 5 8 5 6 1 2 The AS 400 is reachable but you cannot access AS 400 datas 5 8 5 6 2 ORA 28509 4 ette t Past i eua celie a ida 5 9 5 6 2 1 HS is missing from your tnsnames ora filet sss 5 9 5 6 2 2 HS Notun Correct Place ioni eee be He ree tees 5 9 5 6 2 3 TCP IP Configuration nece ee om etre med ie ede e oe 5 9 5 6 2 4 Gateway Listener is NOtUpis ii ici ita onde Ee oe 5 9 5 6 3 ORA 285T1l A Te ee E EE ERE EU eee 5 9 5 6 3 1 Gateway or Listener is Not Up sse ene e ene ne eene 5 9 5 6 3 2 Set Fully Qualified host domain Name sss 5 9 5 6 4 QORA 28500 icti ie ee iiie tbe beet i reti e er iron 5 10 5 6 5 ORAS12154 Lesen irn E BERT be Tu 5 10 5 7 Troubleshooting TCP IP Configuration see eene nennen 5 10 5 7 1 Option 10 of CFGTCP Work with TCP IP Host Table Entries 5 10 5 7 2 Option 12 of CFGTCP Change TCP IP Domain CHGTCPDMN ue 5 10 Configuring the Gateway 6 1 Gateway Command reisens ne eet eee edi de ep ede ee t er rei deities 6 1 6 1 1 Running the Commands nen nene n n
112. efer to Step 2 Verify Journaling on page 4 7 9 7 4 Retrieving Results Sets Through Passthrough Oracle Database Gateway for DB2 400 provides a facility to retrieve results sets from a SELECT SQL statement that is issued through passthrough In the example below the values for a single field are selected from all the rows of the foreign table PT TABLE and these values are inserted into the local Oracle Database table PT TABLE LOCAL Refer to Oracle Database Administrator s Guide for additional information 9 7 4 1 Example DECLARE Developing Applications 9 13 CRS binary integer RET binary integer VAL VARCHAR2 10 BEGIN CRS DBMS HS PASSTHROUGH OPEN CURSORGgtwlink DBMS HS PASSTHROUGH PARSEGgtwlink CRS SELECT NAME FROM PT TABLE BEGIN RET 0 WHILE TRUE LOOP RET DBMS HS PASSTHROUGH FETCH ROWGgtwlink CRS FALSE DBMS HS PASSTHROUGH GET VALUESGgtwlink CRS 1 VAL INSERT INTO PT TABLE LOCAL VALUES VAL END LOOP EXCEPTION WHEN NO DATA FOUND THEN BEGIN DBMS OUTPUT PUT LINE END OF FETCH DBMS HS PASSTHROUGH CLOSE CURSORGgtwlink CRS END END END 9 8 Converting DB2 400 Data Types to Oracle Data Types To move data between applications and the underlying database the gateway binds data values from a host variable or literal to a data type that is understood by the underlying database Oracle tools expect Oracle data types Consequently the gateway maps values from D
113. effect until you shut down and restart the gateway that is specified inthe Existing instance name parameter of the command 6 1 6 CHGGTWDBG Change Debugging Parameters This command is used for isolating the cause of a suspected gateway problem Use CHGGTWDBG only under the guidance of a representative from Oracle Support Services On the OS 400 command line use the CHGGTWDBG command or use the GO CMDORAGTW command and specify option 5 then press Enter Enter the appropriate instance name and press Enter The panel in Example 6 8 Change Oracle Gateway Debugging Option Panel appears For more information about the value choices move the cursor to the value on the panel and press PF1 Example 6 8 Change Oracle Gateway Debugging Option Panel Change Oracle Gateway Debugging Options System AS400A 6 8 Oracle Database Gateway for DB2 400 Installation and User s Guide Gateway Commands Type choices for debugging options press Enter Existing instance name ORACLE Name Use gateway debugging version NORMAI NORMAL DEBUG Gateway pause during job start NORMAI NORMAL PAUSE Gateway show GETENV messages NORMAI NORMAL YES Gateway continue after error NORMAI NORMAL CONTINUE Gateway hang on error NORMAI NORMAL HANG Gateway hang time in minutes DAY NORMAL 1 10080 HOUR DAY WEEK Gateway trace level
114. em or gateway usually a SQL 7008 filename in library not valid for this operation Inthis case enter CRTJRNRCV to create the journal receiver and then enter CRTJRN to create the journal Then start journaling the specified file by using the STRIRNPF command Refer to the Isolation Level section of the IBM reference for DB2 400 for more information about journaling and its parameters 4 5 4 Step 3 Journal the ORACLE2PC File This step is required only if you are planning to issue a distributed update between the Oracle Database 10g server and the gateway any OS 400 stored procedures are executed through the gateway any OS 400 User Defined Function executed anywhere other than in a SOL SELECT statement User Defined Functions executed in a SOL SELECT statement cannot cause any changes to the DB2 400 data Journal the ORACLE2PC physical file which records two phase commit transactions You must journal this file before issuing a distributed transaction to the AS 400 To journal the file enter STRJRNPF FILE instance name ORACLE2PC JRN library journal where instance name isthename that is given to the gateway when it is installed library isthe name of the library where the journal is located journal is the name of the journal 4 5 5 Step 4 Configure Oracle Net Configure Oracle Net on the Oracle Database 10g server Refer to Chapter 5 Oracle Net for more information 4 5 6 Step 5 Remove Observabili
115. ement to create a private synonym if you do not have database administrator authority Using the Gateway 7 3 CREATE SYNONYM EMPDB2 FOR SCOTT EMP gateway 7 4 Accessing the Gateway To access the gateway complete the following steps on the Oracle Database 10g Server 1 Login to the Oracle Database 10g server 2 Create a database link to the AS 400 database with the following syntax CREATE DATABASE LINK AS400 CONNECT TO userid IDENTIFIED BY password USING as400 3 Retrieve data from the AS 400 database using one of the following methods Ifthe CONNECT TO clause of the database link has specified ORACLE as the user ID then this query retrieves data from the EMP file in the ORACLI library using the name ORACLE as the AS 400 user profile E SELECT FROM EMP AS400 This query retrieves the EMP file in the library or collection CORPDATA using the name ORACLE as the AS 400 user profile The ORACLE OS 400 user profile must have the appropriate AS 400 privileges to access the CORPDATA EMP file SELECT FROM CORPDATA EMP AS400 The following messages are displayed if insufficient privileges were granted to Oracle user ERROR at line 1 ORA 28500 Connection from Oracle to non Oracle system returned this message SQL0551 Not authorized to object EMP in CORPDATA type FILE ORA 02063 preceding 2 lines from AS400 4 Update data on the AS 400 5 Before attempting to update a file on the A
116. ennen nnne 6 2 6 1 2 CRTORAGTWI Copy the Gateway sss eee e enne nennen 6 3 6 1 3 CHGORANET Change Network Parameters eene 6 5 6 1 3 1 Changing the ORA SOLNET CHO File see eee ee eee ene 6 5 6 1 3 2 Changing the ORA LISTEN CHO File seen 6 6 6 1 4 CHGORAPJE Change Prestarted Job Parameters sees 6 6 6 1 5 CHGORATUN Change Initialization Parameters eee 6 6 6 1 6 CHGGTWDBG Change Debugging Parameters sess 6 8 6 1 7 CRTORADDB Create Data Dictionary Views sse 6 9 6 1 8 CHGRECOPRE Change Recovery Profile Parameters see 6 9 6 2 Setting Optional P arameters nete itte nece rire lis 6 10 6 2 1 Retrieving Datas mii onsere oieee iei Ine a nid nre ith endorse 6 10 6 2 2 Data Conversi n 6 11 6 2 3 Read Only Gateway miis il rete ad E Pe ade 6 11 Using the Gateway 7 1 Database Link Behavior n gina dedo lata 7 1 7 1 1 Creating Database Links usce eie tere merde tene ecrire eee 7 1 7 1 2 Creating Database Links Using Oracle Net sess eee 7 2 7 1 8 Closirig Database Links entente ter ene e e e de e ere etae 7 2 7 1 4 Accessing Data through Database Links sse eee 7 2 7 1 5 Dropping Database Links tette rti tere ree robe ier bed 7 8 7 1 6 Examining Available Database Links eese eee eene 7 8 7 1 7 Limiting the Number of Active Database Links esses eee 7 3 7
117. entation errors incorrect output Oracle external error a abnormal termination program loop performance missing functionality 10 3 1 Documentation Errors When reporting documentation errors you are asked to provide the following information document name document part number date of publication page number Describe the error in detail Documentation errors can include erroneous documentation and omission of required information 10 3 2 Incorrect Output In general an incorrect output error occurs whenever an Oracle utility produces a result that differs from written Oracle product documentation When describing errors Error Messages Diagnosis and Reporting 10 3 of incorrect output you need to describe in detail the operation of the function in error Be prepared to describe your understanding of the proper function the specific Oracle documentation that describes the proper operation of the function and a detailed description of the incorrect operation If you think you have found a software bug then be prepared to answer the following questions Does the problem occur in more than one Oracle tool Examples of Oracle tools are SQL Plus and Oracle Developer Whatare the exact SOL statements that are used to reproduce the problem a What are the full version numbers of the AS 400 system Oracle Database Oracle gateway and related Oracle software What is the problem an
118. er resources The database and application administrators of a distributed database system are responsible for managing the necessary database links defining paths to the gateway Database links are discussed in detail in the Oracle Database Administrator s Guide Information for using database links with the gateway is given here in this manual 7 1 1 Creating Database Links To create a database link in your Oracle Database and to define a path to the gateway usethe CREATE DATABASE LINK statement The CONNECT TO clause specifies the remote user ID and password to use when creating a session in the gateway If you do not specify a user ID and password in the CONNECT TO clause then the Oracle logon user ID and password are used Using the Gateway 7 1 The USING clause specifies a tnsnames ora connect descriptor 7 1 2 Creating Database Links Using Oracle Net Oracle Net is required The following syntax creates a database link to access information in the DB2 400 database using Oracle Net CREATE DATABASE LINK dblink CONNECT TO userid IDENTIFIED BY password USING tns name entry where dblink is the complete database link name such as gateway userid istheuserID that is used to establish a session in the remote AS 400 system It must be authorized to any table or file on the DB2 400 server that is referenced in the SOL commands The user ID cannot be longer than ten characters password isthe pass
119. erations sees eee 9 20 9 9 SOL F ticlotis nindian A eet e ARE ree ete eee astro retener ve TI IN 9 20 9 10 Oracle Server SOL Construct Processing sssssssssssseeeeeneeeeennnene 9 21 9 10 1 SELECT without the FOR UPDATE Clause sese 9 22 9 10 2 SELECT FOR UPDATE INSERT and DELETE Clauses eee 9 22 9 11 Oracle Server and DB2 400 Differences sees nnennnernnr enters enne 9 22 9 11 1 Oracle Bind Variables Et eta eode e be red bee HS 9 23 9 11 2 Performing Zoned Decimal Operations sss eene 9 23 9 12 Oracle Data Dictionary Emulation in a DB2 400 Server eese 9 23 9 12 1 Using the Gateway Data Dictionary sees eee eee ee enn nennen 9 23 9 12 2 DB27400 Special Registers enne eee eret re detener 9 23 9 13 Oracle Developer Forms Compatibility eee nene 9 24 Error Messages Diagnosis and Reporting 10 1 Message and Error Code Processing sess 10 1 10 1 1 Mapping DB2 400 Error Messages to Oracle Error Messages sees 10 1 10 1 2 Interpreting Gateway Message Formats sse eene 10 2 10 1 3 Diagnosing Errors Detected by the Oracle Database 10g Server sss 10 2 10 2 Contacting Oracle Support Services sess eee nennen nennen 10 3 10 3 Error CatepoOries iiseai ieri et eme tee eec n ida 10 3 10 3 1 Documentation Errors cccccccccssscessscescessecsssceesc
120. ery Language SOL execution is also faster because statements issued by an application are parsed and translated once and can then be reused by multiple applications Version 10g Release 2 gateways leverage the enhancements in the Oracle Database 10g server and you can quickly extend those benefits to your non Oracle data 1 2 1 Advantages of the Gateway Oracle Database Gateway for DB2 400 enables Oracle client applications to access DB2 400 through SQL The gateway and Oracle Database 10g server together create the appearance that all data resides on a local Oracle Database 102 server though data might be widely distributed If data is moved from a DB2 400 database to an Oracle Database then no changes in client application design or function are needed The gateway handles all differences in data types and SOL functions between the application and the database Oracle Database Gateway for DB2 400 gives you the power to integrate your heterogeneous systems into a single seamless environment enabling you to make full use of existing hardware and applications throughout your corporate wide environment You can eliminate the need to rewrite applications for each configuration and avoid the tedious error prone process of manual data transfer Together with the Oracle world class tools networking and data server technology the Oracle Database Gateway for DB2 400 sets the standard for seamless enterprise wide information access 1 2 1 1 T
121. es 5 4 Configuring Oracle Net for TCP IP AS 400 When you install the gateway you are prompted for a listener port through which TCP IP connections will be established By default a listener job starts when the gateway subsystem starts The listener monitors the TCP IP port that you specified and accepts incoming connections directed to that port After a connection is made the listener transfers control to a server job by using the Oracle Net bequeath mechanism Internal process communication IPC between the listener and the server job is achieved through AS 400 local APPC devices The listener then redirects the client connection to a randomly generated port that is assigned to the server job This process is the Oracle Net inherit mechanism For more information about Oracle Net refer to the Oracle Database Net Services Administrator s Guide and the Oracle Database Net Services Reference Figure 5 2 demonstrates a TCP IP connection flow Figure 5 2 TCP IP Connection Flow AS 400 1521 IBM Listener TCP IP APPC Gateway ime XXXX Server Client Note You may need to refer to the IBM Information Center for information about TCP IP concepts and terminology setting up TCP IP and using TCP IP Search for tcp ip setup in the Information Center Correct configuration of TCP IP on the AS 400 ensures that the gateway functions properly
122. escription C 10 TABLE_PRIVILEGES description C 11 USER_CATALOG description C 11 USER_COL_COMMENTS description C 11 USER CONS COLUMNS description C 11 USER CONSTRAINTS description C 12 USER DB LINKS examining available database links 7 3 SER INDEXES description C 12 SER OBJECTS description C 14 SER SYNONYMS description C 14 SER TAB COLUMNS description C 15 SER TAB COMMENTS description C 16 SER TABLES description C 16 SER USERS description C 17 SER VIEWS description C 18 a Grec eu Ww WHERE clause copying data refreshed 7 10 SELECT without the FOR UPDATE clause 9 22 wireless communication 1 4 writing to DB2 400 database read and write access 9 2 WRKTRCLOG command agent trace files A 3 Displaying File Members A 2 make sure instance library is in library list before using this command A 3 Z zoned decimal operations 9 23 Index 13 Index 14
123. essing data 7 2 behavior 7 1 closing 7 2 connecting to DB2 400 databases 9 2 creating 7 1 data dictionary views ALL DB LINKS 7 3 DBA DB LINKS 7 3 USER DB LINKS 7 3 dropping links 7 3 examining 7 3 function in the gateway 1 7 limiting 7 3 using Oracle Net 7 2 database triggers extended database services 1 3 date and time operations Oracle and DB2 400 9 18 date arithmetic restriction 2 3 DATE data type performing Date and Time operations 9 18 dates 21st century main topic 9 19 TO_DATE function 9 19 DB_DOMAIN parameter Oracle Database Server 2 5 DB2 400 Application Server 1 7 cursors maximum number 7 3 database read 9 2 write 9 2 differences from Oracle server 9 22 executing stored procedures 9 6 GRAPHIC data type support and NCHAR 9 16 native SQL 1 5 native stored procedures journal the ORACLE2PC table 1 5 using DB2 400 stored procedures with the gateway 9 5 server compatibility with SOL form 1 9 triggers 7 8 VALUE function SOL differences 1 9 DBA DB LINKS data dictionary view 7 3 DBMS HS PASSTHROUGH EXECUTE IMMEDIAT Efunction 9 12 DCL issue native DCL from Oracle environment 1 5 DD basic tables restriction 2 4 DDL Oracle applications use native DDL 1 5 debugging parameters changing 6 8 DECnet protocol 1 3 default configuration of gateway as commit point site 1 6 DELETE clause Oracle server cannot post process 9 22 design gateway architecture 1 7 developing appli
124. estrictions as DB2 400 parameter markers For example the following statement fragments are not allowed WHERE x IS NULL WHERE X y For more information about DB2 400 parameter marker restrictions refer to the IBM reference for DB2 400 9 11 2 Performing Zoned Decimal Operations A zoned decimal field is described as packed decimal on an Oracle database However an Oracle application such as a Pro C program can insert any supported Oracle numeric data type into a zoned decimal column The gateway converts this number into the most suitable data type Data can be retrieved from DB2 400 into any Oracle data type provided it does not result in loss of information 9 12 Oracle Data Dictionary Emulation in a DB2 400 Server The gateway can optionally augment DB2 400 database catalogs with data dictionary views that are modeled after the Oracle Database data dictionary These views are based on the dictionary tables in DB2 400 presenting the catalog information in views familiar to Oracle Database users The views that are created during the installation of the gateway automatically limit the data dictionary information that is presented to each user based on the privileges of that user 9 12 1 Using the Gateway Data Dictionary The gateway data dictionary views provide you with an Oracle like interface to the contents and use of the DB2 400 data dictionary Some of these views are required by Oracle products You can query the gate
125. evel 8 6 Oracle Database Gateway for DB2 400 Installation and User s Guide Streams Replication DECLARE iscn NUMBER Variable to hold instantiation SCN value BEGIN iscn DBMS FLASHBACK GET SYSTEM CHANGE NUMBER DBMS APPLY ADM SET TABLE INSTANTIATION SCN Source object name scott emp source database name ORAv92 instantiation scn iscn apply database link strmdblink your domain com BEGIN DBMS APPLY ADM DROP APPLY apply name apply 2 db2400 BEGIN DBMS APPLY ADM CREATE APPLY queue name strmadmin streams queue apply name apply 2 db2400 apply database link strmdblink your domain com apply captured true BEGIN DBMS STREAMS ADM ADD TABLE RULES table name scott emp Streams type gt apply Streams name apply 2 db2400 queue name strmadmin streams queue include dml true include ddl false Source database ORAv92 Turn on tracing for the apply process be careful this generates alot of output BEGIN DBMS APPLY ADM SET PARAMETER apply name apply 2 db2400 parameter gt trace level value 127 END Administering the Gateway 8 7 BEGIN DBMS APPLY ADM SET PARAMETER apply name apply 2 db2400 parameter disable on error value n BEGIN DBMS APPLY ADM START APPLY apply name apply 2 db2400 BEGIN DBMS CAPTURE ADM START CAPTURE capture name d
126. everage the advanced features of the Oracle Database Oracle SQL functions that are not listed in the compatible or translated lists includer earlier are compensated SQL functions 9 10 Oracle Server SQL Construct Processing Special circumstances and restrictions for processing Developing Applications 9 21 9 10 1 SELECT without the FOR UPDATE Clause A SELECT without the FOR UPDATE clause can be handled in one of three ways The Oracle database post processes SELECT statements without the FOR UPDATE If the entire WHERE clause of the SELECT statement is acceptable syntax for DB2 400 then it is given to DB2 400 to perform If part but not all of the WHERE clause of the SELECT statement uses features that are not available in DB2 400 then the WHERE clause is split between the DB2 400 system and the Oracle database The portion of the WHERE clause that is acceptable for DB2 400 is sent to DB2 400 The Oracle database post processes the results of the DB2 400 SELECT and applies the Oracle specific WHERE clause elements This results in DB2 400 doing as much of the WHERE clause as possible If the entire WHERE clause is not acceptable for DB2 400 then an unqualified SELECT without the WHERE clause is sent for DB2 400 and the Oracle database post processes the entire WHERE clause clause Most Oracle SELECT statements are supported
127. extends to non Oracle data the benefits of the Internet and Oracle Net and the Oracle client server and server server connectivity software These powerful features include Application server support Any Internet or intranet application that can access data in an Oracle database can also incorporate information from data stores that are accessible through the gateways Web browsers can connect to the Oracle Database using any application server product that supports Oracle software Implicit protocol conversion Oracle and Oracle Net can work together as a protocol converter allowing applications to transparently access other data stores on platforms that do not support the client network protocol For example an application can use SPX IPX to communicate with an Oracle Database 10g server which can use TCP IP to communicate with the gateway and another data store on a non SPX IPX platform Advanced Security Non Oracle data can be protected from unauthorized access or tampering during transmission to the client This is done by using the hardware independent and protocol independent encryption and CHECKSUM services of the Advanced Security a Wireless communication Oracle Mobile Agents an Oracle industry leading mobile technology enables wireless communication to Oracle Database 10g server or any databases that are accessible through the gateways This gives field personnel direct access to enterprise data from mobile
128. file is not journaled 5 6 5 ORA 12154 This error indicates that a missing or incorrect tnsnames file has been specified or that an incorrect spelling or service name has been specified in the tnsnames file This is generally an Oracle Net issue not a gateway issue 5 7 Troubleshooting TCP IP Configuration You may encounter errors during the test phase of the gateway installation even though you are able to issue a PING If you do encounter such errors then please check the panels that were used to define the host name Specifically check the values presented when using option 12 of the CFGTCP command 5 7 1 Option 10 of CFGTCP Work with TCP IP Host Table Entries Table 5 3 TCP IP Host Table Entries Option Internet Address Host Name 10 10 5 40 AS400 AS400 US ORACLE COM 127 0 0 1 LOOPBACK LOCALHOST Enter Internet addresses and host names Host names in the Work with TCP IP Host Table Entries panel must include both the unqualified and the qualified names the one that includes the domain name Option 10 depends upon Option 12 Option 12 defines your host name as well as your domain name Under Option 10 you must enter your host name and your hostname domain_name This may seem redundant but if the gateway is not configured correctly for TCP IP then the gateway will not make a connection regardless of the output from the PING command Any changes to CFGTCP will require you to restart the gateway TCP c
129. for DB2 400 Installation and Users Guide Column Name Description SAMPLE SIZE N A LAST ANALYZED N A DEGREE N A INSTANCES N A PARTITIONED N A TEMPORARY N A GENERATED N A SECONDARY N A BUFFER POOL N A USER STATS N A DURATION N A PCT DIRECT ACCESS N A ITYP OWNER N A ITYP NAME N A PARAMETERS N A GLOBAL STATS N A DOMIDX STATUS N A DOMIDX OPSTATUS N A FUNCIDX STATUS N A JOIN INDEX N A IOT REDUNDANT PKEY N A DROPPED N A C 7 ALL OBJECTS Objects that are accessible to the user Column Name Description OWN ER OBJ ECT_NAME SUBOBJECT_NAME OBJ DATA OBJECT ID OBJECT TYPI ECT ID ti CREATED LAST_DDL_TIME TIM mM Ei ESTAMP STATUS MPORARY Owner of the object N A N A N A N A N A N A Name of object Object number of the object Type of object State of the object Data Dictionary Views C 5 ALL SYNONYMS Column Name Description GENERATED N A SECONDARY N A C 8 ALL SYNONYMS All synonyms that are accessible to the user Column Name Description OWNER SYNONYM NAMI E TABLE OWNER TABLE NAME DB LINK Owner of the synonym Name of the synonym Owner of the object referenced by the synonym Name of the object referenced by the synon
130. fragments not allowed 9 23 passthrough function 9 13 retrieving results sets through passthrough 9 13 SQL expressions NOT supported for date arithmetic 2 3 SQL Plus command to check character set of an existing database B 4 starting and stopping the gateway 8 4 streams code for simple table replication 8 5 to find primary authorization ID being used by gateway using DB2 400 special registers 9 23 triggers for updating non Oracle data 7 8 using the SOL Plus COPY command 7 9 coded character set identifier CCSID B 2 co existence between gateways 4 1 COLUMN PRIVILEGES view description C 9 columns DECIMAL 9 20 INTEGER 9 20 NUMERIC 9 20 PACKED 9 20 ROUTINE_NAME 9 6 ROUTINE_SCHEMA 9 6 SMALLINT 9 20 commands examples of codes and commands see code examples summary of gateway commands 6 1 CFGTCP troubleshooting TCP IP configuration 5 10 CHGGTWDBG command description 6 8 purpose 6 1 CHGORANET assigning port number 5 7 command description 6 5 purpose 6 1 trace files 5 3 CHGORANET changing parameter values in ORA SQLNET 5 3 CHGORAPJE command description 6 6 purpose 6 1 CHGORATUN changing BLOCKSIZE 6 11 command description 6 6 data conversion 6 11 gateway configuration B 1 language specification B 1 purpose 6 1 CHGRECOPRF caution to synchronize recovery password 6 10 8 5 purpose 6 2 CLRPFM FILE clearing LOG file LISTENER member A 1 COPY general restrictions 2 3 note regarding l
131. haracters Recovery user profile ORANEW Name Recovery user password ORANEW 1 10 characters Pre start JODS 4 4 RR US YES YES NO TCP IP port number 1521 1024 65534 Auxiliary storage pool id q 1 16 Install Data Dictionary SUDpDO t eos xU rw ea Rr Rus NO YES NO gt Fl Help F4 Prompt F9 Retrieve F12 Cancel The first two entries are set from the previous screen and cannot be changed Existing instance name isa name from one character to six characters long The default is whatever is in the ORA HOME data area If you ADDLIBLE instance name then the value in the ORA HOME data area will be the existing instance name Instance name to create isaname from one character to six characters long You must fill in the following choices Instance password isthe password for the user ID that will be created for the new user profile The user ID has the same name as the newly created instance name You should change this password for security reasons Recovery user profile isa profile name that you enter or you can use the default of the newly created gateway instance name Recovery user password isa password for the recovery user profile The default is the newly created gateway instance name You should change this for security reasons Use the OS 400 CHGUSRPRF command to make the change Then use the CHGRECOPRF command refer to CHGRECOPRE Change Recovery Profile Pa
132. hat are associated with conditions that are common to most relational databases 10 1 1 Mapping DB2 400 Error Messages to Oracle Error Messages DB2 400 error messages SOLSTATE codes are mapped to Oracle database error codes Notice that multiple DB2 400 SOLSTATE codes can refer to the same Oracle database error code Table 10 1 SQLSTATE Codes Mapped to Oracle Server Error Codes Description SQLSTATE Code Oracle Server Error Code No rows selected 02000 0 Unique index constraint violated 23505 ORA 0001 Table or view does not exist 52004 or 42704 ORA 00942 Object name too long Therefore 54003 or 42622 ORA 00942 object does not exist Error Messages Diagnosis and Reporting 10 1 Table 10 1 Cont SQLSTATE Codes Mapped to Oracle Server Error Codes Description SQLSTATE Code Oracle Server Error Code Insufficient privileges 42501 ORA 01031 Divisor is equal to zero 01519 or 01564 ORA 01476 10 1 2 Interpreting Gateway Message Formats Error messages are generally accompanied by additional message text beyond the text associated with the Oracle message number The additional text includes details about the error Most gateway messages exceed the 70 character message area in the Oracle SOLCA Use SOLGLM or OCIErrorGet in the programmatic and Oracle Call Interfaces that you use with the gateway to view the entire message Refer to the Oracle Database PL SQL User s Guide and Reference for i
133. he HS keyword is not needed 5 2 2 Step 2 Specify SID Names Oracle servers that are using TCP IP to connect to a gateway must specify a system identifier SID name in the TNSNAMES ORA file that is used to connect to the AS 400 The SID name is used on the AS 400 to start the server The listener uses the SID name to reference a CSI file The CSI file uses a TP NAME to start the required CL program in the gateway server Several SID names are configured in the shipped system and are listed in the following table Set the SID to ORAGATE in order to use a prestarted job ORAGATE would give the best response time Setting the SID to other values should be done in special situations as directed by Oracle support personnel 5 4 Oracle Database Gateway for DB2 400 Installation and User s Guide Checklists for Configuring Oracle Net Table 5 1 SID Names That Are configured in the Shipped System SID CSI TP NAME CL Program ORAGATE ORAGATE ORAGWTPJ RUNORAGTTP ORAGTPJ ORAGTPJ ORAGWTPJ RUNORAGTTP ORAGTEJ ORAGTEJ ORAGWTEJ RUNORAGTTE ORAGTTC ORAGTTC ORAGWTTC RUNORAGTTT The following table provides the name and usage of each SID Table 5 2 SID Names and Their Usage SID Usage ORAGATE uses one of the prestarted TCP IP jobs ORAGTPJ is an alias for ORAGATE ORAGTEJ starts a new gateway job ORAGTTC starts a gateway job and a
134. he index C 12 Oracle Database Gateway for DB2 400 Installation and User s Guide Column Name Description INDEX TYPE TABL TABL TABL UNIO COMP F OWNER F _NAME F _TYPE U R ENESS ESSION PREFIX LENGTH TABL Ini I SPACE NAME INI TRANS MAX MIN MAX NEXT TRANS INITIAL EXTENT EXTENT EXTENTS EXTENTS PCT INCREASE PCT INCL FREE THRESHOLD FREE UDE COLUMN ISTS IST GROUPS PCT FREE LOGGING BL EV AVG EAF_B EL LEAF_BLOCKS DISTINCT_KEYS OCKS_PER_KEY AVG DATA_B OCKS_PER_KEY CLUST STAT NUM SAMP E SIZI ERING FACTOR US ROWS Al AST_ANALYZED DEGR F F INSTANCES PARTITIONED Type of Index Owner of the indexed object Name of the indexed object Type of the indexed object Uniqueness status of the index N A N A Name of the table space containing the index N A N A N A N A N A N A N A N A N A N A N A N A N A Depth of the index from its root block to its leaf blocks A depth of one indicates that the root block and the leaf block are the same Number of leaf blocks in the index Number of distinct indexed values
135. her than DB2 400 for example the CRTPF and CRTSRCPF commands can create such files AII flat files have an externally described attribute The externally described attribute refers to whether or not a file is defined by an external template All DB2 400 tables are externally described You can access externally described flat files through the gateway in the same way that you access DB2 400 tables Files in OS 400 that are created with the CRTPF command which specifies DDS source are externally described Files in OS 400 that are created with the CRTPF command and without specifying DDS source are not externally described The latter type of file can be accessed by the gateway However only one column will be available the column name will be the same as the member name and the data will be described as RAW n where n is the record length that was used in the CRTPF command Files in the integrated file system IFS are not accessible to the gateway because they are not accessible to DB2 400 Using the Gateway 7 5 The syntax for accessing flat files is library file where library is the library name file isthe file member flat file name If flat files have multiple members then you need to create a view for each member or combination of members 7 6 Performing Distributed Queries The gateway technology can execute distributed queries that join with data from the Oracle Database 10 server and DB2 400 and any other data sto
136. hese recovery operations are complete Oracle will then continue with the normal gateway login which had been suspended Using the Gateway 7 7 During the recovery process the server uses the recovery user ID and password to login to the system and resolve pending transactions After recovery is complete the gateway executes the user s login request This recovery process is transparent to the user The recovery user ID and password are initially set in the panel shown in Example 4 2 Install Oracle Database Gateway Panel With Name Choices If you need to change these values then you must run the CHGRECOPRF command 7 7 Replicating in a Heterogeneous Environment Oracle Database Gateway for DB2 400 provides a number of options for replicating Oracle data and non Oracle data throughout the enterprise 7 7 1 Oracle Database Server Triggers When updates are made to the Oracle database synchronous copies of Oracle data and non Oracle data can be maintained automatically by using Oracle database triggers 7 7 2 Oracle Materialized View Oracle Database Gateway for DB2 400 can use the Oracle Materialized View snapshot feature to automatically replicate non Oracle data into the Oracle database This complete refresh capability of Oracle Materialized View can be used to propagate a complete copy or a subset of the non Oracle data into the Oracle database at user defined intervals 7 8 Copying Data from Oracle Server to DB2 400
137. imitations 7 9 CREATE COLLECTION setting Instance Name parameter during gateway installation 4 3 CREATE MATERIALIZED VIEW automatically and asynchronously copy DB2 400 server data 7 10 CREATE MATERIALIZED VIEW copying DB2 400 server data 7 10 CREATE TABLE copying DB2 400 server data 7 9 CRTLF accessing OS 400 file members 7 5 CRTORADDB command description 6 9 purpose 6 1 CRTORAGTWI purpose 6 1 CRTUSRPRF message queue created in QUSRSYS library 1 8 DSPLIND displaying line description parameters 5 6 DSPPGM verifying installation and version of gateway 4 7 ENDSBS starting and stopping the gateway 8 3 EXECUTE using Oracle stored procedures 1 5 INSERT copying data from DB2 400 server 7 9 JOIN accessing tables in multiple databases 9 2 ORAMON gateway architecture 1 7 starting the Oracle Gateway Monitor 8 1 PING verifying host name 5 8 RUNSQLSTM required for cloning an instance 3 2 SBMJOB removing IBM observability 4 9 SQL Plus COPY DB2 400 Server to Oracle server 7 10 COPY limitation when copying to the DB2 400 server 7 9 COPY Oracle server to DB2 400 Server 7 8 INSERT not supported to copy tables from Oracle server to gateway 7 9 SQL creating database links using Oracle Net 7 2 STRORALSN starting TCP IP listener manually 5 8 STRSBS OS 400 command to start gateway 1 7 WRKTRCLOG make sure instance library is in library list before using this command A 3 commit confirm protocol 1 6 co
138. ing SOL statement SELECT FROM EMP AS400 WHERE HIREDATE 03 MAR 81 you must use the following syntax SELECT FROM EMP AS400 WHERE HIREDATE TO DATE 03 MAR 81 In a programmatic interface program using a character bind variable for the qualifying date value you must use the following SOL statement SELECT FROM EMP AS400 WHERE HIREDATE TO DATE 1 This SQL notation does not affect SOL statement semantics when the statement is executed against an Oracle table The statement remains the same across Oracle and DB2 400 data stores The DATE requirement does not apply to input bind variables in Oracle date seven byte binary format The gateway recognizes such values to be dates Note Special considerations exist for using date or time data types in stored procedures with DB2 400 Refer to Known Problems on page 2 2 for more information 9 8 6 Dates in the 21st Century Oracle recommends that you set the Oracle Database 10g server default NLS DATE FORMAT parameter to a format including a four digit year Use the TO DATE function to enter dates in the twenty first century You can use any date format which includes a four character field for the year For example Developing Applications 9 19 TO DATE 2008 07 23 YYYY MM DD can be used in any SELECT INSERT UPDATE or DELETE statement 9 8 7 Performing Numeric Data Type Operations DB2 400 performs automatic conve
139. ing native DB2 400 SQL This release of Oracle Database Gateway for DB2 400 enables retrieval of result sets from queries that are issued with passthrough The syntax is different from the DBMS HS PASSTHROUGH EXECUTE IMMEDIATE function Refer to Retrieving Results Sets Through Passthrough on page 9 13 for more information 9 7 1 Using the DBMS HS PASSTHROUGH EXECUTE IMMEDIATE Function To execute a passthrough SQL statement by using the DBMS HS PASSTHROUGH EXECUTE IMMEDIATE function use the following syntax number of rows DBMS HS PASSTHROUGH EXECUTE IMMEDIATEedblink DB2 400 sql where number of rows isa variable that is assigned the number of rows that are affected by the passthrough SQL execution For DDL statements a zero is returned for the number of rows affected dblink isthename of the database link that is used to access the gateway DB2 400 sql isa valid DB2 400 nonquery SQL statement except CONNECT COMMIT or ROLLBACK DB2 400 SQL statements that cannot be dynamically prepared are rejected by DB2 400 The SOL statement that is passed by the DBMS HS PASSTHROUGH EXECUTE IMMEDIATE function must be a character string For more information regarding DB2 400 SQL statements refer to the IBM reference for DB2 400 9 12 Oracle Database Gateway for DB2 400 Installation and User s Guide Passing DB2 400 SQL Statements Through the Gateway
140. ing steps to configure your Oracle Database server computers for TCP IP Refer to the networking documentation for your Oracle Database server computer for more information about configuring it 1 Step 1 Add a TCP IP Connect Descriptor to tnsnames ora 2 Step 2 Specify SID Names 5 2 1 Step 1 Add a TCP IP Connect Descriptor to tnsnames ora Use the TCP IP connect descriptor in the tnsnames ora file on the Oracle Database 10g server connect name DESCRIPTION ADDRESS PROTOCOL TCP PORT 1521 HOST host_name CONNECT DATA SID csi name HS where connect name is the name of the connection This name must be unique within the tnsnames ora file The connect_name corresponds to the value that is found with the USING keyword on a CREATE DATABASE LINK command TCP isthe TCP protocol that is used for TCP IP connections 1521 isthe default port number that is used by the Oracle Net listener on the AS 400 Change this value if you have changed the listener port number host name isthe TCP IP host name of your AS 400 computer It may be a DNS name such as myas400 us mycompany com or an IP dotted value such as 10 2 3 7 csi name is the name ofthe communication side information CST file that is used by the listener to start the server Usually the csi name is ORAGATE Refer to Step 2 Specify SID Names on page 5 4 for more SID names HS This keyword is mandatory The right hand side of t
141. ious technology to the Oracle Database you can use the gateway to move the DB2 400 data into Oracle databases The Oracle Database Gateway for DB2 400 enables you to Integrate heterogeneous database management systems so that they appear as a single homogeneous database system Read and write data from Oracle applications to data in DB2 400 in addition to reading and writing data from Oracle applications to data in any Oracle server DB2 400 Large objects can be read only at this time 1 2 10g Release 2 Gateways The Oracle Database 10g provides the foundation for the next generation of the Gateways which deliver enhanced integration capabilities by exploiting Oracle Heterogeneous Services Heterogeneous Services is a component of the Oracle Database 10g server The Oracle Database 10g server provides the common architecture for future generations of the gateways For detailed information about Oracle Heterogeneous Services refer to Oracle Database Heterogeneous Connectivity Administrator s Guide Version 10g Release 2 gateways are closely integrated with the Oracle Database 10g server and they enable improved performance and enhanced functionality while still providing transparent integration of Oracle data and non Oracle data For example connection initialization information is available in the local Oracle Database 10g server reducing the number of round trips and the amount of data that is sent over the network Structured Qu
142. laptop commuters 1 2 1 4 Dynamic Dictionary Mapping Before an application can access any information the application must be told the structure of the data such as the names of columns of a table and their lengths Many other products require administrators to manually define this information in a separate data dictionary stored in a hub Applications then access information using the hub dictionary instead of the native dictionaries of each database This approach requires a great deal of manual configuration and maintenance on your part Administrators must update the data dictionary in the hub whenever the structure of a remote table is changed Inefficient duplication is eliminated with Oracle Database Gateway for DB2 400 The simple setup of the gateway does not require any additional mapping The gateway uses the existing native dictionaries of each database Your applications access data using the dictionaries that are designed specifically for each database which means that no redundant dictionary need ever be created or maintained Oracle Database Gateway for DB2 400 eases the application development and maintenance by allowing you to access any data using a uniform set of SQL Changes to the location storage characteristics or table structure do not require any changes to your applications ANSI and ISO standard SQL are supported along with powerful Oracle extensions 1 4 Oracle Database Gateway for DB2 400 Installation and User
143. le name or password or both for the User Profile name or password that the gateway uses when directed to perform transaction recovery by the Oracle server Transaction recovery is necessary when any failure occurs during a distributed transaction The User Profile name and password are created by the OS 400 CRTUSRPRF command or are changed by the OS 400 CHGUSRPRF command Example 6 9 Change Recovery Profile Parameters Panel Change Oracle Recovery Profile Parameters System AS400A Type choices press Enter Configuring the Gateway 6 9 Existing instance name ORACLE Name Recovery Profile Name ORACLE Profile Name Recovery Profile Password Confirmation of Recovery Profile Password Password Password Fl Help F4 Prompt F9 Retrieve F12 Cancel Enter the User Profil e name in the Recovery Profile Name field or leave it as it is Enter the password for the User Profile name on the next two lines The password is stored in an encoded form Because this password is no longer visible as plain text extra care may be required to keep the recovery password synchronized between the AS 400 computer and the gateway Refer to the following note Caution You must keep the gateway values for recovery user profile and password synchronized with the OS 400 values for recovery user profile and password The gateway recovery user ID and password must be valid to the AS 400 at all times If the recovery pa
144. ma dd Mona C 10 DUAL E C 10 OTIGREGISTBER aee bere s md x Lei med veio nibo tb te PIE ERE C 10 TABLE PRIVILEGES 5 6 penes np OD en aD eH epe ra C 11 USER GP WP MEO Cor C 11 USER COL COMMEN TS 2 1 2 eret repete to He ee LR hac P Eeee E aE Cr ek ee RC REB C 11 USER CONS EOLUMES cet ecce eene eto ete ee ee tenere te e e racer iaa C 11 USER CONSTRAINTS Aroero 1 cerea ii e e rtt Eee AT rir ha ee o ro odo ie eed eer esta C 12 USER INDEXBS rtc rnt oe iere en PP ORDEI He PEE HE Me HIA e eere C 12 USER OBJECT Siiri iriti nn mamando C 14 USER SSYNONYMS 3 22 35 c6 iter ate de Pe e ed d etr deba OPE C 14 C 26 C 27 C 28 C 29 C 30 Index USERSTAB COLUMNS t etes A e e tend tir Das EA Rees Soh deeds C 15 USERZTAB COMMENTS otocivinntascinion aint eripere erede sep E a e E E ei cu even bead C 16 USER TABLES ocio ettet ren ede peperere a dene E teo C 16 USER USERS ucraniano abe C e OE dE I bee a d dr eee aepo C 17 USER VIEWS rodent silent bi C 18 Preface Read this guide if you are responsible for performing tasks such as installing and configuring the gateway administering the gateway using the gateway Understand the fundamentals of database gateways and OS 400 before using this guide for installation or system administration Intended Audience This guide is intended for anyone installing configuring and administering the gateway as well as for developers writing applications that access remote host database
145. mber 3695959 GRAPHIC 127 in DB2 is incorrectly mapped to VARCHAR2 in ORACLE DB Bug number 3693493 CREATE VIEW AS SELECT incorrectly changed the length of columns Bug number 3629826 CCSID 930 5026 Gateway trace not displayed in WRKTRCLOG 2 3 Known Problems The problems documented in the following section are specific to the Oracle Database Gateway for DB2 400 and are known to exist in this release of the product These problems are currently being addressed by Oracle Refer to the respective bugs for the current status of each problem If you have any questions or concerns about these problems then please contact Oracle Support Services A current list of problems is available online Contact your local Oracle office for information about accessing this online information 2 2 Oracle Database Gateway for DB2 400 Installation and User s Guide Known Restrictions 2 4 Known Restrictions The restrictions documented in this section are known to exist for the products in this release of the Oracle Database Gateway for DB2 400 Refer to Chapter 9 Developing Applications for information about limitations when developing your applications 2 4 1 General Restrictions The following sections describes general restrictions when using the gateway 2 4 1 1 SQL Development Kit Product 5722 ST1 As described in Bug 3616425 the Gateway needs to have product 5722 ST1 SOL Development Kit installed on the AS
146. ments 4 6 Julian dates restriction 2 4 K known problems 2 2 known restrictions date arithmetic 2 3 DB2 400 components 2 4 GLOBAL NAMES initialization parameter 2 5 OCI 2 4 snapshots 2 3 L Languages gateway architecture 1 5 libraries created as a SOL collection journaling 4 7 data dictionary created in library OTGDB2 4 5 installation procedure creates new or uses pre existing but empty library 4 3 library files LOG file members A 1 library is created gateway changes to AS 400 system 1 8 member names in gateway library files A 1 new tables 9 13 Oracle Net parameter files are installed in gateway instance library 5 3 ORASRVLIB 1 8 QSYS checking authority of QWSETP program 4 3 QUSRSYS 1 8 starting the Oracle Gateway Monitor 8 1 syntax for accessing flat files 7 6 target library using passthrough statements to create tables 9 13 listener client connection 5 2 Oracle Net for AS 400 architecture 5 2 prestarted jobs 5 3 TNS 5 2 LISTENER member clearing entries to reduce size of LOG file A 1 in LOG file A 1 of ORA file A 2 LISTENLOCK member of ORA file A 2 literal values restriction 2 4 local domain name TCP IP configuration 5 7 local host name TCP IP configuration 5 7 LOG file members Oracle Net A 1 logical file members OS 400 logical files 7 5 LONG data type Index 7 converting character string data types 9 18 restriction 2 6 LONG VARCHAR data type 9 18
147. mmit point site in distributed transactions 7 7 COMMIT POINT STRENGTH setting 7 7 commitment control considerations for using stored procedures with DB2 400 9 7 journaling default setting 4 6 compatible SOL functions defined 9 20 compensated SQL functions defined 9 21 CONCAT translated SOL function 9 21 concurrent connections TCP IP memory 3 1 CONNECT BY clause limitation 9 22 not supported in SOL 2 5 CONNECT TO clause accessing data through databaselinks 7 2 accessing the gateway 7 4 creating databaselinks 7 1 gateway security 8 4 connection errors 5 8 convert SOL to make compatible with DB2 400 server 1 9 COPY command from Oracle server to DB2 400 Server compared with triggers 7 8 examples 7 9 general restrictions 2 3 note regarding limitations 7 9 copying data from the DB2 400 server 7 9 from the Oracle Database Server 7 9 to DB2 400 server 7 8 the gateway instance CRTORAGTWI command 6 3 CREATE COLLECTION command installation procedure setting required parameters 4 3 CREATE DATABASE LINK command and define a path to the gateway 7 1 CREATE MATERIALIZED VIEW command automatically and asynchronously copy DB2 400 server data 7 10 complete refresh propagate a complete copy or a subset 7 10 CREATE SNAPSHOT command replaced by CREATE MATERIALIZED VIEW command 7 10 CREATE TABLE command copy data from DB2 400 server to Oracle Database server 7 9 passthrough and native DB2 400 SOL 1 5 CRTL
148. n a Ensure that you log on with a user profile that has the SECADM JOBCTL ALLOBJ and SYSCFG special authorities The user profile OSECOFR as distributed by IBM has these authorities a Ensure that a library or collection with the same name as the instance name to be created does not already exist If it does exist then it must be empty or must have only those objects that would result from a SOL CREATE COLLECTION command After entering 1 at the main menu panel or CRTORAGTWI the panel in Example 6 2 Create Oracle Database Gateway Panel is displayed Example 6 2 Create Oracle Database Gateway Panel Create Oracle Database Gateway V10 2 0 1 0 System AS400A Type choices press Enter Existing instance name ORACLE Name up to six characters Instance name to create Name up to six characters gt Fl Help F4 Prompt F9 Retrieve F12 Cancel Enter the name of the new instance to be created and press Enter to continue The panel in Example 6 3 Create Oracle Database Gateway Panel with New Values appears Example 6 3 Create Oracle Database Gateway Panel with New Values Create Oracle Database Gateway V10 2 0 1 0 Configuring the Gateway 6 3 System AS400A Type choices press Enter Existing instance name ORACLE Name up to six characters Instance name to create ORANEW Name up to six characters Instance password ORANEW 1 10 c
149. n Entering option 10 in the Opt field displays a call stack for a specific job 8 6 Working with Gateway Job Locks Specify option 11 in the Opt field next to a specific job in order to display and work with object locks that are held by the gateway This option displays the OS 400 Work with Job Locks panel 8 7 Displaying Open Files Specify option 14 in the Opt field next to a specific job to show the files that are open for that specific job This option displays the OS 400 Display Open Files panel 6 8 Gateway Security The Oracle user ID and password are passed over the database link to the gateway to authorize gateway users to DB2 400 objects If the CONNECT TO clause is specified when creating the database link then the user ID and password that are sent to the gateway are those that are specified in this clause If the CONNECT TO clause is omitted from the database link specification then the Oracle user ID and password of the task that is using the database link are passed to the gateway for authorization 8 4 Oracle Database Gateway for DB2 400 Installation and User s Guide Streams Replication Caution You must keep the gateway recovery user ID and password synchronized with the OS 400 User Profile and password The gateway recovery user ID and password must be valid to OS 400 at all times If when transaction recovery is being attempted by Oracle the password for the recovery user ID is not valid or if the recover
150. n 4000 characters the column will be seen by the Oracle database as a LONG column In SQL Plus the default buffer length for a retrieved LONG column is 80 characters For this reason if you wish to retrieve a DB2 400 column that will be interpreted as an Oracle LONG column then you must set the SQL Plus buffer length correctly If the buffer size is not sufficient to contain the column value then you will receive the following error message ORA 28528 Heterogeneous Services datatype conversion error To work around this problem reset the size of the buffer using the SET LONG command in SQL PLUS or Server Manager In addition if you use a program to retrieve a LONG column on the Oracle client then you must retrieve the entire LONG column in one call to the Oracle client You cannot retrieve the LONG column in sections through the gateway 2 6 Oracle Database Gateway for DB2 400 Installation and User s Guide 3 System Requirements This chapter describes the OS 400 hardware and software requirements for the Oracle Database Gateway for DB2 400 It contains the following sections Hardware Requirements on page 3 1 a Software Requirements on page 3 2 Documentation Requirements on page 3 2 Distribution Kit on page 3 2 3 1 Hardware Requirements The following sections describe the hardware requirements for installing and running the gateway 3 1 1 Processor The gateway requires any AS 400 processor that supports
151. n DB 400 show slight but potentially problematic differences Some other SQL functions have different semantics in Oracle SOL and DB2 400 SQL For details refer to SQL Functions on page 9 20 in Chapter 9 Developing Applications 2 4 3 DB2 400 SQL Limitations The following sections describe restrictions when using SOL 2 4 3 1 Oracle Bind Variables Oracle bind variables become DB2 400 SOL parameter markers when used with the gateway The bind variables are therefore subject to the same restrictions as DB2 400 SOL parameter markers For example the following statements are riot allowed WHERE x IS NULL WHERE x y 2 4 3 2 CONNECT BY Is Not Supported Oracle Database Gateway for DB2 400 does not support CONNECT BY in SELECT statements 2 4 3 3 Oracle Incremental Materialized View Refresh Incremental Materialized View refresh is not supported between the gateway and the Oracle Database 10g server However Oracle complete Materialized View is supported between the gateway and the Oracle Database 10g server Release Information 2 5 2 4 3 4 LONG Data Types Any DB2 400 CHAR or VARCHAR column with a length greater than 4000 bytes is considered an Oracle LONG data type by the gateway When using a SELECT statement in SQL Plus for any DB2 400 GRAPHIC column that is defined with a length greater than 2000 characters or for any DB2 400 CHAR or VARCHAR column that is defined with a length greater tha
152. n has a default SBCS CCSID of perhaps 37 the corresponding column in the Oracle DB will be a CHAR 15 Globalization Support B 5 Character Set Expansions B 6 Oracle Database Gateway for DB2 400 Installation and Users Guide C Data Dictionary Views This appendix includes the gateway data dictionary views that are accessible to all users of an Oracle database Most views can be accessed by any user with SELECT privileges for DB2 400 catalog tables If a dictionary item is described with N A in this appendix then that means that the item is not available for the gateway Such items default to zero for numeric columns or to blanks or some fixed value for VAR CHAR columns This appendix contains the following sections a ALL CATALOG on page C 2 a ALL COL COMMENTS on page C 2 ALL CONS COLUMNS on page C 2 ALL CONSTRAINTS on page C 3 ALL IND COLUMNS on page C 3 a ALL INDEXES on page C 4 a ALL OBJECTS on page C 5 a ALL SYNONYMS on page C 6 ALL TAB COLUMNS on page C 6 ALL TAB COMMENTS on page C 7 ALL TABLES on page C 7 a ALL USERS on page C 9 ALL VIEWS on page C 9 COLUMN PRIVILEGES on page C 9 DICTIONARY on page C 10 DUAL on page C 10 a OTGREGISTER on page C 10 a TABLE PRIVILEGES on page C 11 USER CATALOG on page C 11 USER COL COMMENTS on page C 11 USER CONS COLUMNS on page C 11 USER CONSTRAINTS on page C 12 Data Dictionary Views C 1 ALL C
153. nd stop an Oracle gateway instance work with Oracle gateway jobs start the TCP IP listener job display the gateway call stack work with the gateway job locks display the open files of the gateway 8 2 Starting the Oracle Gateway Monitor To execute the Oracle gateway monitor you must log on to the gateway instance user ID and have the gateway instance library included in your library list To start the Oracle gateway monitor enter the following command ORAMON Administering the Gateway 8 1 The main menu for the Oracle Gateway Monitor is displayed as shown in Example 8 1 Monitor Oracle Subsystems Example 8 1 Monitor Oracle Subsystems Posit ion to Monitor Oracle Subsystems System AS400A Update frequency OFF Type Options press Enter F1 He 2 Start 4 End 5 Work with 7 Listen 10 Display call stack 11 Work with locks 14 Display open files Job Aux Aux CPU Name Type Status Number I O KB Secs ORACLE SBS ACTV 0 0 0 LISTENER LIS SELW 015826 564 3386 2 8 RUNORAGTTP PJ PSRW 015823 124 3725 1 6 RUNORAGTTP PJ PSRW 015822 138 3737 1 5 lp F2 Change list F3 Exit F5 Refresh F8 Start auto update The columns in the panel in Example 8 1 Monitor Oracle Subsystems represent the following Opt is used to perform various functions on subsystems and jobs For example option 2 starts a gateway subsystem Enter an option number next to the subsystem or job name and then press Enter You can select optio
154. nds Panel is displayed Example 6 1 CMDORAGTW Oracle Commands Panel CMDORAGTW Oracle Commands System AS400A Select one of the following Create instance Change network settings Change prestart job settings Change gateway initialization settings Change debugging options Change Oracle Data Dictionary objects batch Change Recovery Profile Parameters 1o U C ho FP Selection or command gt F3 Exit F4 Prompt F9 Retrieve F12 Cancel Enter the choice number 1 2 3 4 5 6 or 7 or the corresponding command name a CRTORAGTWI a CHGORANET m CHGORAPJE m CHGORATUN a CHGGTWDBG CRTORADDB 6 2 Oracle Database Gateway for DB2 400 Installation and Users Guide Gateway Commands a CHGRECOPRF After you have made your selection press Enter to continue The panel for the command is displayed Command panels are described under the individual commands Note For more information about these choices move the cursor to the value on the panel and press PF1 6 1 2 CRTORAGTWI Copy the Gateway You can have as many copies of a gateway instance on your system as you want After you have installed a release 10 gateway if you need another instance of the gateway before you issue the CRTORAGTWI command then you must a Shut down the instance you are about to copy Use the ENDSBS command with the instance name as the operand to perform the shutdown operatio
155. nformation about SOLGLM and the Oracle Call Interface Programmer s Guide for information about OCIErrorGet Gateway messages use the following format ORA nnnnn error message text DB2 400 error messages ORA 02063 preceding n lines from dblink for example select from scott dummy DB21link ERROR at line 1 ORA 00942 table or view does not exist SQL0204 DUMMY in SCOTT type FILE not found ORA 02063 preceding 2 lines from dblink where nnnnn isan Oracle error number If nnnnn is between 28500 and 28559 then the message is from the gateway If it is not in this range then it is a mapped error message In the example above the value for nnnnn is 00942 error message text isthetext of the message that is associated with the error In the example above the value for error message text is table or view does not exist DB2 400 error messages are additional messages that are generated by DB2 400 In the example above the value for DB2 400 error messages is SQLO204 DUMMY in SCOTT type FILE not found n isthe total number of DB2 400 error messages lines that are referenced above the code entry line In the example above the valuefor n is 2 dblink isthename ofthe database link that is used to access the gateway In the example above the value for dblink is DB2link 10 1 3 Diagnosing Errors Detected by the Oracle Database 10g Server If an error is detected by the Oracle Database 10g server then the DB2 400 er
156. nonym N A C 26 USER_TAB_COLUMNS Columns of user tables views and clusters Column Name Description TABLE_NAME COLUMN NAMI DATA TYPI DATA TYPI Ins P E MOD DATA TYP E OWNER DATA Ll EET DATA PRECISION DATA SCALE NULLABLE NGTH COLUMN ID DATA DEFAULT NUM DISTINCT OW_VALUE DEFAULT_LENGTH HIGH VALUI DENSITY NUM BUCKI AST_ANA El NUM_NULLS ETS YZED SAMPLE_SIZE CHARACTE R_SET_NAME Table view or cluster name Column name Data type of the column N A N A Maximum length of the column in bytes N A Digits to the right of decimal point in a number Asks if the column allow null values Value is n if there is a NOT NULL constraint on the column or if the column is part of a PRIMARY key Sequence number of the column as created N A N A Number of distinct values in each column of the table Second lowest value for tables with more than three rows These statistics are expressed in hexadecimal notation for the internal representation of the first 32 bytes of the values Second highest value for tables with more than three rows These statistics are expressed in hexadecimal notation for the internal representation of the first 32 bytes of the values N A N A N A N A N A N A Data Dictionary Views C 15 USER
157. ns for more than one subsystem or job at a time The option numbers are listed in the panel of Example 8 1 Name displays the names of Oracle subsystems and jobs Type is the type of entry for this line a ASJ automatically started job m LIS listener PJ prestarted job waiting for start request SBS subsystem SRV server job UNK unknown Status is the status of the subsystem or job that is associated with the line For subsystems type SBS ACTV active subsystem 8 2 Oracle Database Gateway for DB2 400 Installation and Users Guide Starting and Stopping the Gateway ERROR error while determining subsystem status INACTV inactive subsystem NOAUTH userhas no authorization to start or stop subsystem For jobs refer to the Help information for the Status column of the WRKACTJOB panel Job Number is the OS 400 job number for job related lines It is blank for subsystem lines Aux I O is the total number of auxiliary storage I O operations that were performed Aux KB is the current amount of auxiliary storage occupied by the job in kilobytes units of 1024 bytes CPU Secs is the total CPU time used in seconds To display specific gateway subsystems and jobs you must enter the subsystem into the Oracle gateway monitor subsystem by pressing PF2 This displays a panel where you can enter the name of the Oracle gateway subsystems The panel in Example 8 1 Monitor O
158. nt Mode form module Off You should also ensure that at item and block level the primary key is set to TRUI E For more information refer to your Oracle Forms documentation 9 24 Oracle Database Gateway for DB2 400 Installation and User s Guide 10 Error Messages Diagnosis and Reporting This chapter discusses error messages that are generated by Oracle Database Gateway for DB2 400 the diagnosis of suspected Oracle errors and the requirements for documenting these errors to Oracle Support Services This chapter contains the following sections Message and Error Code Processing on page 10 1 Contacting Oracle Support Services on page 10 3 a Error Categories on page 10 3 10 1 Message and Error Code Processing The gateway architecture includes a number of separate components Any of these components can detect and report an error condition while processing a SOL statement that refers to one or more DB2 400 database tables An error condition can be complex involving error codes and supporting data from multiple components In all cases the application receives a single Oracle error code on which to act When possible an error code from DB2 400 is converted to the Oracle error code that is associated with the same logical condition Error code mapping is provided to support application designs that test for and act upon specific error conditions The set of mapped errors is limited to those errors t
159. nt e a e RR I s EP gestes B 2 Supported Languages and Territories B 3 Oracle Server and Client Configuration sse eene B 4 Message Availability diede tine Bec d EU ERR A ES oli ede teed B 5 DB2 400 GRAPHIC Support 1 eite tee eese d re DP sert pe B 5 Character Set ExpansiOnSi ie eibi dire n Roh ERE Rohr iia ab B 5 Data Dictionary Views C 1 C 2 C 3 C 4 C 5 C 6 C 7 C 8 C 9 C 10 C 11 C 12 C 13 C 14 C 15 C 16 C 17 C 18 C 19 C 20 C 21 C 22 C 23 C 24 C 25 AEL CATALOG tica laos C 2 ADL COL COMMENTS tete tite rr tre a te dere e ite e eere eere oen C 2 ALT CONS COLUMNS 1e etre AAS reete ee i ERE PUER PEE ERES C 2 ALL CONSTRAINTS itte ertt et era ee e eee ie eee boe eid ko eua C 3 ADDE IND COLUMN S 2 feo Sivas eatin tree o Petre ee eee ites dees cues e tree equestre C 3 ALL INDEXES 5 agas aara Ri ite ee Hee ER DH P MP or BERE ANE C 4 ALL OBJECTS ihri iishepitisto n meh ne le t ese tre oo ixi bits iste Hber t dvd C 5 ALL SYNONYMS 1 2 e rri p ope ep o dadas C 6 AL Ts TAB COEUMNJS ertet neret iieri reporte pret i Pero ud ie Pes eek Dre verae io shee C 6 ALL TAB COMMEN PS aieeaa reae to aeea rete reed tee at Cre di C 7 ALL SLABLES nghe oet edo E eoe irte oes C 7 ADL USERS teen RE met ated metre exte i pede i Silos AE S C 9 AEL VIEWS 3 nma RN NN C 9 COLUMN PRIVILEGES siii A A eet e eve cas e ee YE ede eran C 9 DIEHONARY 12 dieeeetisecee a ete sete eoe ere ete oed ee stre e
160. ny single user process can use concurrently with a single SOL statement Refer to the Oracle Database Administrator s Guide for additional information about limiting the number of active database links 7 2 Using DB2 400 Cursors The maximum number of DB2 400 cursors that the gateway can open per Oracle Database session is 200 Although the gateway can open 200 cursors other Oracle Database or DB2 400 limits might affect how many cursors can actually be opened for a specific application Use the CHGORATUN command to change the maximum number of cursors 7 3 Using the Synonym Feature You can provide complete data location and network transparency by using the synonym feature of the Oracle Database 10g server When a synonym is defined you do not need to know the underlying table or network protocol that is being used A synonym can be public which means that all users can make reference to the synonym A synonym can also be defined as private which means that every user must have a synonym defined to access the underlying DB2 400 table Refer to the Oracle Database 10g server documentation for details about the synonym feature The following statement creates a system wide synonym named EMPDB2 in the Oracle Database for the SCOTT EMP file in the DB2 400 server CREATE PUBLIC SYNONYM EMPDB2 FOR SCOTT EMP gateway Only those users with database administrator authority can create public synonyms You can use a similar stat
161. o use an AS 400 stored function DB2 400 user defined function in a SOL command without specifying that name in the above IFS file then you will get the Oracle error ORA 06571 function xxxxx does Developing Applications 9 9 not guarantee not to update database After changing this file it is recommended that you bring down and then re start the instance Other than the declaration of the function name in the above IFS file the gateway does not require any other special definitions in order to invoke the DB2 400 stored function In Figure 9 8 an Oracle application calls the empfunc stored function that is defined in DB2 400 Figure 9 8 Executing DB2 400 Stored Functions DB2 400 empfunc Oracle Application Stored Function in Schema some schema erii El vai Oracle E Database Tn ca mn El Oracle Database erii Gateway for DB2 400 DB2 400 From the perspective of the application executing the DB2 400 stored function is no different that invoking a stored function at a remote Oracle instance 9 6 1 Executing DB2 400 Stored Functions from Applications In order for an application to invoke a DB2 400 stored function it is first necessary to create the DB2 400 stored function on the DB2 400 system by using the procedures that are documented in the IBM reference for DB2 400 Next if you are going to reference that function in a SOL DML st
162. olumn you can insert only double width characters into VAR GRAPHIC columns in other words the string to be inserted must consist entirely of DBCS characters Attempts to insert a mixed byte string into a VAR GRAPHIC column will result in an error For example attempting to insert a value represented by a mixed byte string constant into a VAR GRAPHIC column will result in a SQL0105 error Mixed or Graphic String Constant Not Valid An ASCII client might attempt to use the following INSERT statement INSERT INTO mytable graphcol tg4db2400 values AxxB where A and B are the normal ASCII single width characters and xx designates a double width character in one of the Far Eastern ASCII based character sets such as JA16SJIS ZHS16CGB213280 ZHT16BIG5 or KO16KSC5601 There is no unambiguous way in which to insert such data into a VAR GRAPHIC column The problem exists because no DBCS character corresponds to each of the single byte characters This version of the Oracle Database Gateway for DB2 400 provides a workaround but the workaround carries a risk for data integrity If you desire to choose this path then you must realize that if you attempt to INSERT a string with a single byte character then that string may be returned to you entirely as a DBCS string in a subsequent SELECT as a double byte character The rules are as follows 1 With no options specified the user must ensure th
163. omain CHGTCPDMN 5 7 CMDORAGTW Oracle Commands 6 2 Create Oracle Database Gateway 6 3 Create Oracle Database Gateway Panel with new values 6 3 Install Oracle Database Gateway 4 4 Install Oracle Database Gateway Panel With Name Choices 4 4 Verify Oracle Install 4 5 parameters ARRAY BLOCK SIZE 9 4 ARRAY BLOCK SIZE setting 6 10 AUTOCREATE CONTROLLER 5 6 data dictionary 6 9 DATABASE DOMAIN 6 7 DB_DOMAIN Oracle9 server 2 5 debugging 6 8 Index 9 HS DB DOMAIN Heterogeneous Services initialization parameter 2 5 HS PRC FETCH REBLOCKING 9 3 HS RPC FETCHSIZE 9 3 initialization 6 6 network 6 5 OPEN_LINKS 7 3 optional 6 10 READONLY 6 11 required 4 3 RPC FETCH REBLOCKING YES is default value 9 3 array size for SELECT 9 3 initialization parameter notes 6 7 may be changed by CHGORATUN command 9 3 RPC FETCH SIZE array size for SELECT 9 3 initialization parameter notes 6 7 may be changed by CHGORATUN command 9 3 setting optional parameters for gateway configuration 6 10 setting required parameters for gateway installation 4 3 SQLNET ORA file name structure 5 3 passthrough and Native DB2 400 SQL introduction 1 5 examples 9 13 function 9 12 result sets example 9 13 retrieval 9 13 sending SQL statement directly to DB2 400 without being interpreted by Oracle server 9 12 performance issues 10 4 PING command troubleshooting gateway configuration 5 10 verifying host name 5 8 PL SQL rou
164. on B 1 number of DB2 400 cursors 7 3 purpose 6 1 CHGRECOPRF command caution to synchronize recovery password 6 10 8 5 command description 6 9 password for recovery user profile 6 4 purpose 6 2 CHGUSRPRF command password for recovery user profile 6 4 clauses CONNECT BY example of when not supported 9 22 CONNECT TO gateway security 8 4 FOR UPDATE SELECT statements without FOR UPDATE 9 22 with no column names 9 22 USING creating a database link 7 2 WHERE split processing 9 22 CLRPFM FILE command clearing LOG file LISTENER member A 1 code examples accessing AS 400 file members 7 5 accessing data through database links 7 2 bind variable restrictions 2 5 checking authority of QWTSETP program in QSYS library 4 3 commands to give authority to gateway ID 4 2 CONNECT TO user ID provides implicit qualification for unqualified tables 7 2 converting character string data types 9 18 copying data from DB2 400 to Oracle 7 9 copying data with SOL Plus commands 7 9 date and time operations 9 19 distributed query SOL command one 7 6 distributed query SOL command two 7 6 Index 2 dropping databaselinks 7 3 executing stored procedures 9 6 gateway appearance to application programs 9 1 INSERT statement from ASCII client double byte character support 9 16 with FORCE SB option 9 17 interpreting gateway message formats 10 2 journaling 4 7 numeric data type operations 9 20 Oracle bind variables statement
165. on in distributed transaction 7 7 using Oracle stored procedures with the gateway 9 5 using Oracle stored procedures 9 4 verifying the version 4 7 gateway monitor see Oracle gateway monitor GATEWAY NATIONAL LANGUAGE parameter 6 7 GENERAL linkage convention 9 7 GENERAL WITH NULLS linkage convention 9 7 GLOBAL_NAMES known restrictions 2 5 Globalization Support B 1 CCSID Oracle language parameters must match B 2 supported character sets B 2 supported languages B 3 supported territories B 3 gateway commands CHGORANET B 1 CHGORATUN B 1 gateway configuration changing language settings B 1 CHGORATUN language specification B 1 message availability B 5 Oracle server and client configuration B 4 overview B 1 GO CMDORAGTW command invoking main menu 6 2 GRAPHIC data type support for DB2 400 B 5 H hanging condition connection problems 5 8 hardware requirements 3 1 heterogeneous services enhanced integration capabilities 1 2 initialization parameters known restrictions for DB2 400 2 5 host name TCP IP configuration 5 7 HS parameter mandatory keyword using TCP IP connect descriptor 5 4 HS DB DOMAIN Heterogeneous Services initialization parameter 2 5 HS RPC FETCH SIZE parameter 9 3 HS RPC FETCH REBLOCKING parameter 9 3 IBM DATE data type 9 19 implicit protocol conversion 1 4 incorrect output errors 10 3 Incremental Materialized View refresh gateway restriction 2 5 inherit mechani
166. onfiguration is documented in Configuring Oracle Net for TCP IP AS 400 on page 5 6 in this chapter 5 7 2 Option 12 of CFGTCP Change TCP IP Domain CHGTCPDMN Table 5 4 Change TCP IP Domain Option Host name Domain name AS400A US ORACLE COM The host name and the domain name combine to form the qualified host name For example AS400A as illustrated in Option 12 combines with US ORACLE COM to form the qualified host name AS400A US ORACLE COM 5 10 Oracle Database Gateway for DB2 400 Installation and User s Guide Troubleshooting TCP IP Configuration Oracle Net 5 11 5 12 Oracle Database Gateway for DB2 400 Installation and User s Guide 6 Configuring the Gateway After installing the gateway you can run gateway commands and change gateway parameters This chapter contains the following sections Gateway Commands on page 6 1 Setting Optional Parameters on page 6 10 6 1 Gateway Commands All gateway parameters are changed with gateway commands which are accessed through a menu system These commands and their menus are described in this section The gateway comes with commands to do the following tasks copy clone the gateway change the most common gateway parameters change the level of tracing and debugging All commands can be used after a gateway is installed The following table summarizes each command and its purpose The Menu Choice is used when
167. or DB2 400 The Oracle Database Gateway for DB2 400 must be installed on an AS 400 system and is responsible for issuing the dynamic SQL calls to DB2 400 The gateway can access files in an OS 400 SOL Collection or files that are externally described to DB2 400 The gateway is not started as an Oracle instance and has no continuously running background processes The gateway is started either by using the OS 400 STRSBS command or by using the ORAMON command Individual gateway tasks run in a subsystem as does the LISTENER task for TCP IP 4 DB2 400 Server The DB2 400 database is the database that is being accessed by the gateway Multiple Oracle Database 10g servers can access the same gateway A single gateway installation can be configured to access one and only one DB2 400 server because there is only one instance of a DB2 400 database for each AS 400 system Multiple gateways can be installed on a single AS 400 system Figure 1 1 Architecture of the Oracle Database Gateway for DB2 400 Oracle Net Connection Oracle Database Gateway for DB2 400 DB2 400 Database Link Oracle Net or Local Conection Oracle Database 1 8 How the Gateway Works The gateway has no database functions of its own Instead it provides an interface by which the Oracle Database 10g server can direct SOL operations to a DB2 400 database The gateway that is supporting the
168. or refer to the IBM manual for AS 400 Globalization Support for additional information about AS 400 CCSID codes B 3 1 Supported Character Sets Oracle Database Gateway for DB2 400 supports the following languages and values for character set Note that the character sets that are marked with an asterisk are the Euro versions of the immediately preceding character set Table B 1 Character Sets Supported by Oracle Database Gateway for DB2 400 Description Character Set OS 400 CCSID Austrian German D8EBCDIC273 273 Austrian German Euro D8EBCDIC1141 1141 Traditional Chinese ZHT16DBCS 937 Simplified Chinese ZHS16DBCS 935 Danish Norwegian DK8EBCDIC277 277 Danish Norwegian Euro DK8E8CDIC1142 1142 Eastern European EE8EBCDIC870 870 Finnish Swedish S8EBCDIC278 278 Finnish Swedish Euro S8EBCDIC1143 1143 French France F8EBCDIC297 297 B 2 Oracle Database Gateway for DB2 400 Installation and Users Guide Table B 1 Cont Character Sets Supported by Oracle Database Gateway for DB2 400 Description Character Set OS 400 CCSID French France Euro F8EBCDIC1147 1147 German Germany D8EBCDIC273 273 German Germany Euro D8EBCDIC1141 1141 Greek EL8EBCDIC875 875 Hebrew IW8EBCDIC424 424 Italian I8EBCDIC280 280 Italian Euro I8EBCDIC1144 1144 Japanese JA16
169. ort number you should use CHGORANET only under the guidance of a representative from Oracle Support Services Enter the new values and press Enter to continue The new values do not take effect until you shut down and restart the gateway that was specified inthe Existing instance name parameter of the command Example 6 4 Change Oracle Network Parameters Panel Change Oracle Database Gateway System AS400A Type choices press Enter Existing instance name ORACLE Name TCP IP port number 1521 1024 65534 Client trace level OFF OFF USER ADMIN 16 Listener trace level OFF OFF USER ADMIN 16 Server trace level OFF OFF USER ADMIN 16 Regenerate files NO YES NO gt Fl Help F4 Prompt F9 Retrieve F12 Cancel 6 1 3 1 Changing the ORA SQLNET_CHG File Some network parameters are documented in the ORA SOLNET file but are not displayed on the CHGORANET panel These parameters cannot be changed directly by editing the ORA SOLNET file They must be changed in the ORA SQLNET_CHG file 1 Usean OS 400 editor e g SEU to change parameter values in ORA SOLNET CHG 2 Onthe OS 400 command line use the CHGORANET command or use the GO CMDORAGTW command and specify option 2 3 Enter the instance name of the gateway and press Enter 4 Specify YES on the Regenerate Files line and press Enter The ORA SOLNET file now
170. oth column and scalar functions Column Functions m AVG COUNT only COUNT DISTINCT expression m MAX m MIN STDDEV one argument SUM Scalar Functions ABS m COS a COSH m EXP 9 20 Oracle Database Gateway for DB2 400 Installation and User s Guide Oracle Server SQL Construct Processing LENGTH char only LN MOD POWER SIN a SINH as SQRT TAN TANH UPPER m VARIANCE one argument 2 Translated Translated SQL functions provide the same functionality but are referenced by different names at the Oracle server and the foreign data store Translated SQL functions include Table 9 2 Some Translated SQL Functions Oracle DB2 400 NVL VALUE LOWER TRANSLATE operator CONCAT Note Use the passthrough feature for native DB2 400 SOL statements when using the VALUE TRANSLATE or CONCAT functions Refer to Passing DB2 400 SOL Statements Through the Gateway on page 9 12 for additional information 3 Compensated Compensated SQL functions are advanced SQL functions that are supported by the Oracle Database and that cannot be expressed or recognized by the foreign data store SQL compensation in the gateways enriches the semantics of the native SOL of a remote data source such as DB2 400 This important feature of the gateway allows application developers and users to l
171. pplications 9 5 In Figure 9 5 an Oracle application calls the empproc stored procedure that is defined to DB2 400 Figure 9 5 Executing DB2 400 Stored Procedures Oracle Application DB2 400 empproc Stored Procedure Bari A Bop Oracle pra sexum Database EFE teat EXE bis Oracle Database Bei Gateway for DB2 400 DB2 400 From the perspective of the application executing the DB2 400 stored procedure is no different than invoking a stored procedure at a remote Oracle Database instance 9 4 1 Executing DB2 400 Stored Procedures from Applications In order for an application to invoke a DB2 400 stored procedure it is first necessary to create the DB2 400 stored procedure on the DB2 400 system by using the procedures that are documented in the IBM reference for DB2 400 After the stored procedure is defined to DB2 400 the gateway is able to access that stored procedure by using a standard PL SQL call For example an employee name JOHN SMYTHE is passed to the DB2 400 stored procedure REVISE SALARY The DB2 400 stored procedure retrieves the salary value from the DB2 400 database to calculate a new yearly salary for JOHN SMYTHE The revised salary returned in RESULT is used to update SAL inthe EMP table of an Oracle Database as follows DECLARE INPUT VARCHAR2 15 RESULT NUMBER 8 2 BEGIN INPUT JOHN SMYTHE MYLIB REVIS
172. pport B 1 DB2 400 Coded Character Set Considerations language territory character set where language is any valid language documented in Table B 2 Supported Languages and Territories on page B 3 territory is optional and defaults to AMERICA Valid values are documented in Table B2 Supported Languages and Territories on page B 3 character set is optional and defaults to WE8EBCDIC37 Valid values are documented in Table B 1 Character Sets Supported by Oracle Database Gateway for DB2 400 on page B 2 The default setting is AMERICAN AMERICA WE8EBCDIC37 To change this setting use the Gateway language field on the CHGORATUN main menu panel The gateway must be shut down and restarted before the new parameter takes effect For more information about the CHGORATUN command refer to CHGORATUN Change Initialization Parameters on page 6 6 B 3 DB2 400 Coded Character Set Considerations If the coded character set identifier CCSID of the AS 400 data field differs from 65535 then the Oracle language parameters must correspond to the CCSID of the AS 400 data field that is being accessed For example if the CCSID of the data field is 280 for Italy then the Oracle character set must be set to I8EBCDIC280 The exception to this is for columns with a CCSID of 13488 UCS 2 The data in these columns will always be converted to the character set that is determined by NLS LANG Contact your DBA
173. py data from the DB2 400 server to the Oracle Database COPY FROM username password8tns alias INSERT destination table USING query where username or password or both are valid at the Oracle Database that is identified by tns alias and where query refers to a remote table that is accessed through a gateway database link The following example selects all rows from the EMP table in DB2 400 and inserts them into the local Oracle EMP table COPY FROM SCOTT TIGERGORACLE SERVER INSERT EMP USING SELECT FROM SCOTT EMP gateway 7 10 Oracle Database Gateway for DB2 400 Installation and User s Guide 8 Administering the Gateway Read this chapter to understand how to administer your gateway either by using the Oracle Gateway Monitor or by entering commands at the command line prompt The following sections are included Overview of the Oracle Gateway Monitor on page 8 1 Starting the Oracle Gateway Monitor on page 8 1 Starting and Stopping the Gateway on page 8 3 Starting the TCP IP Listener on page 8 4 Displaying the Gateway Call Stack on page 8 4 Working with Gateway Job Locks on page 8 4 Displaying Open Files on page 8 4 Gateway Security on page 8 4 Streams Replication on page 8 5 8 1 Overview of the Oracle Gateway Monitor The Oracle Gateway Monitor is a facility that is provided with the Oracle Database Gateway for DB2 400 This facility permits a single interface to perform the following functions start a
174. r Ed E J oraproc2 Oracle Stored Procedure t EN ORA2 2 Tti Hate E If the application needs to maintain location transparency then a synonym can be created 9 4 Oracle Database Gateway for DB2 400 Installation and User s Guide Using DB2 400 Stored Procedures with the Gateway CREATE SYNONYM ORAPROC2 FOR oraproc2 ora2 where oraproc2 is the procedure that is stored in ORA2 After this synonym is created the application no longer needs to use the database link specification for invoking the stored procedure at the remote Oracle instance In Figure 9 3 the INSERT statement in oraprocl1 is used to access a table in the ORA2 instance In the same way Oracle stored procedures can be used to access DB2 400 tables through the gateway In Figure 9 4 empproc is an Oracle stored procedure that subsequently accesses data in DB2 by using the Oracle Database Gateway for DB2 400 Figure 9 4 Using Oracle Stored Procedures with DB2 400 Oracle empproc Oracle Application Stored Procedure Bop mE N anii Oracle amme Database EH di gt Rr EE Lg Oracle Database Gateway for DB2 400 E Ny E DB2 400 As with the Oracle gateway standard PL SOL is used to create and execute the procedure There is no difference in the gateways except that the stored procedure is accessing DB2 400 instead of an Oracle Database Gateway two phase commit proces
175. r this parameter refer to NO mode and UCS support DB2 400 GRAPHIC Support on page 9 16 for more information Example 6 6 Change Oracle Gateway Initialization Parameters first page of panel Change Oracle Gateway Initialization Parameters System AS400A Type choices press Enter Existing instance name ORACLE Name Database Domain WORLD Database Name ORACLE Array block size 100 0 32767 Gateway language american america we8ebcdic37 Language ID NLS LANG V4 Graphic and UCS 2 Compatibility mode NO V4 Graphic and UCS 2 Configuring the Gateway 6 7 Compatibility mode ORAGRAPHA Maximum Date eere ORA MAX DATE Option for CCSID 65535 fields BITDATA BITDATA CHARDATA User Profile CCSID SYSVAL SAME SYSVAL HEX CCSID value gt More Fl Help F4 Prompt F9 Retrieve F12 Cancel Example 6 7 Change Oracle Gateway Initialization Parameters second page of panel Change Oracle Gateway Initialization Parameters System AS400A Type choices press Enter Change Isolation Level CHG CHG CS RR Set gateway for READ ONLY NO YES NO Maximum Number Cursors 200 50 200 RPC Fetch Reblocking YES YES NO RPG Fetal SIZE 5 ses 40000 4000 50000 gt Bottom Fl Help F4 Prompt F9 Retrieve F12 Cancel The new values do not take
176. r to Installation Steps on page 4 2 for information about verifying this value 2 4 1 7 Date Arithmetic Date arithmetic is not supported for SOL statements For example the following SOL expressions do not work date number number date date number datel date2 Release Information 2 3 Refer to Chapter 9 Developing Applications for additional information 2 4 1 8 Julian Dates Support You must use the J option on the TO DATE and TO CHAR functions to receive valid Julian dates 2 4 1 9 GRAPHIC Constants in SQL Commands GRAPHIC constants in SOL statements that are valid in one character set may not be valid as G type or N type GRAPHIC constants in DB2 400 For example graphic constants in character set KOL6KCSC5601 may contain both single width and double width characters GRAPHIC constants in DB2 400 can contain only double width characters Caution Potential Loss of Data Oracle strongly recommends that you do not directly use graphic constants in SOL statements If you do use graphic constants then you should ensure that all of the characters within a graphic constant are double width characters Oracle recommends that you use bind variables if you wish to use such constants The gateway checks the graphic constants found in INSERT statements and can force the single width characters to become double width characters if requested With graphic data resulting from a SELECT the g
177. racle Developer Forms Compatibility on page 9 24 9 1 Gateway Appearance to Application Programs An application that is written to access information in a DB2 400 database interfaces with an Oracle database When developing applications remember the following You must define the object in the DB2 400 database to the Oracle application by use of a database link that is defined at the Oracle DB Your application specifies DB2 400 objects for example tables by qualifying those object names with a database link For example suppose you have defined a database link and named it DB2400 And suppose you have a table named EMP in the local Oracle database and a similarly defined EMPS table in DB2 400 And finally suppose you want to join the two tables to extract some information The following SQL statement retrieves data from both the Oracle database and DB2 400 SELECT EMP EMPNO EMPS SALARY FROM EMP EMPS DB2400 Developing Applications 9 1 WHERE EMP EMPNO EMPS EMPNO Alternatively you can define a synonym or a view referencing the DB2 400 server table and access the information without the database link qualification For example CREATE SYNONYM EMPS FOR EMPS DB2400 SELECT EMP EMPNO EMPS SALARY FROM EMP EMPS WHERE EMP EMPNO EMPS EMPNO You can perform reads and writes of data to a defined DB2 400 database SELECT INSERT UPDATE and DELETE are all valid operations A single transaction c
178. racle Subsystems shows a single gateway subsystem with a single listener and two prestarted gateway jobs To work with a specific gateway subsystem or job you must enter an option number inthe Opt field next to the subsystem or job that is displayed on the main panel In Example 8 1 Monitor Oracle Subsystems the Update frequency field is set to OFF This means that no updates of the information are displayed until you press PF5 If you want the screen to be updated periodically then you need to press PF8 The default update refresh period is 30 seconds but you can set it to a value between 30 seconds and 600 seconds If help is needed at any time then pressing PF1 on any of the panels will display more information about a specific panel 8 3 Starting and Stopping the Gateway Entering a 2 in the Option field on the main panel allows you to start a gateway subsystem ACTV is displayed in the STATUS field of a subsystem SBS when the gateway subsystem is started To start the gateway subsystem you must be authorized to use the STRSBS command To stop the gateway subsystem or a job in a gateway subsystem move the cursor to the Opt field that is next to the subsystem or job that you want to shut down and enter a 4 This ends the specified gateway subsystem or job When the gateway subsystem has shut down INACTV is displayed in the field next to the subsystem or job that ended To shut down the gateway you must be authorized to us
179. rameters on page 6 9 Prestart jobs fprestarts the TCP IP jobs By default two TCP IP jobs are prestarted Use YES to prestart the TCP IP server jobs Use NO to prestart NO TCP IP server jobs You can change how many jobs are prestarted by changing the value of the initial number of TCP IP jobs parameter with the CHGORAPJE command For more information refer to CHGORAPJE Change Prestarted Job Parameters on page 6 6 TCP IP port number will be the same as the port number of the cloned instance You should enter a different port number because each instance requires a unique port number Auxiliary storage poolid uses the default of 1 or you can enter another ID if you have additional auxiliary storage pools defined 6 4 Oracle Database Gateway for DB2 400 Installation and User s Guide Gateway Commands Install Data Dictionary Support Enter YES if you wish to reinstall the Data Dictionary support The defaultis NO If you are copying cloning a gateway then the Data Dictionary was most likely installed when the copied cloned gateway itself was installed In that case you do not need to reinstall the Data Dictionary 6 1 3 CHGORANET Change Network Parameters After entering 2 at the main menu panel or by entering the CHGORANET command enter the appropriate instance name and press Enter The panel in Example 6 4 Change Oracle Network Parameters Panel appears Except when you are changing the value of the TCP IP p
180. ransparency at All Levels By using the Oracle Database Gateway for DB2 400 you can achieve transparency at every level within your enterprise Location transparency 1 2 Oracle Database Gateway for DB2 400 Installation and User s Guide 10g Release 2 Gateways Users can access tables by name without having to understand the physical location of the tables Network transparency The gateways exploit the Oracle Net technology to allow users to access data across multiple networks without concern for the network architecture or protocols Multiple protocols are supported Operating system transparency You can access data that is stored under multiple operating systems without being aware of the different operating systems that hold the data Data storage transparency Data can be accessed regardless of the database or file format Access method transparency You can use a single dialect of SOL for any data store thereby eliminating the need to code for database specific access methods or SOL implementations 1 2 1 2 Extended Database Services The following are some of the more sophisticated Oracle Database 10g server services that are available through the gateway SQL functionality Your application can access all your data using Oracle SOL which is rich in features Advanced Oracle Database 10g server functionalities such as outer joins are available even if the target data stores do not support them in a nativ
181. re for which Oracle provides a gateway These complex operations can be completely invisible to the users requesting the data 7 6 1 Example of a Distributed Query 7 6 1 1 SQL Command Example 1 Assume that dblink DB2 points to a database gateway for DB2 on z OS and assume that dblink ORACLESERVR points to a remote Oracle Database instance and assume that dblink AS400 points to an instance of the Oracle Database Gateway for DB2 400 Then the following example joins data between those three database servers SELECT O CUSTNAME P PROJNO E ENAME SUM E RATE P HOURS FROM ORDERS DB2 O EMPGORACLESRVR E PROJECTS AS400 P WHERE O PROJNO P PROJNO AND P EMPNO E EMPNO GROUP BY O CUSTNAME P PROJNO E ENAME Through a combination of views and synonyms the process of distributed queries may become invisible to the user For example CREATE SYNONYM ORDERS FOR ORDERS DB2 CREATE SYNONYM PROJECTS FOR PROJECTS AS400 CREATE SYNONYM EMP FOR EMPGORACLESRVR CREATE VIEW DETAILS CUSTNAME PROJNO ENAME SPEND AS SELECT O CUSTNAME P PROJNO E ENAME SUM E RATE P HOURS FROM ORDERS O EMP E PROJECTS P WHERE O PROJNO P PROJNO AND P EMPNO E EMPNO GROUP BY O CUSTNAME P PROJNO E ENAME 7 6 1 2 SQL Command Example 2 Using the following SOL statement a user can retrieve information from the three data stores in Example 1 using a single command as follows SELECT FROM DETAILS The results from the SOL command in example 2 are th
182. red method is to use STRSOL to create a DB2 400 collection and then to install the gateway into the library that is associated with that collection Objects that are created later in the collection will be automatically journaled Installing the Gateway 4 3 Example 4 1 Install Oracle Database Gateway Panel Install Oracle Database Gateway at V10 2 0 1 0 Type Choices press Enter Instance name ORACLE Name up to six characters gt Fl Help F4 Prompt F9 Retrieve F10 Additional parameters F12 Cancel C COPYRIGHT ORACLE CORPORATION 1994 2006 After entering a name press Enter The panel in Example 4 2 Install Oracle Database Gateway Panel With Name Choices appears Example 4 2 Install Oracle Database Gateway Panel With Name Choices Install Oracle Database Gateway at V10 2 0 1 0 System AS400A Type Choices press Enter Instance name leere ORACLE Name up to six characters Instance password ORACLE 1 10 characters Prestart JObDS o o oo ooo YES YES NO TCP IP port n mb6r ico lS 1521 1024 65534 Recovery user profile ORACLE Name Recovery user password ORACLE 1 10 characters Auxiliary storage pool id 1 1 16 Install Data Dictionary Support YES YES NO Fl Fl Help F4 Prompt F9 Retrieve F12 Cancel You may change the choices Table 4 1 Name Choices on Install Panel Choice Name Descrip
183. reflects the values that are specified in the ORA SQLNET CHG file Configuring the Gateway 6 5 6 1 3 2 Changing the ORA LISTEN CHG File Some network parameters are documented in the ORA LISTENER file but not displayed on the CHGORANET panel These parameters cannot be changed directly by editing the ORA LISTENER file They must be changed using the ORA LISTEN_CHG file 1 Use an OS 400 editor e g SEU to change parameter values in ORA LISTEN CHG 2 Onthe OS 400 command line use the CHGORANET command or use the GO CMDORAGTW command and specify option 2 3 Enter the instance name of the gateway and press Enter 4 Specify YES on the Regenerate Files line and press Enter The ORA LISTENER file now reflects the values that are specified in the ORA LISTEN CHG file 6 1 4 CHGORAPJE Change Prestarted Job Parameters On the OS 400 command line use the CHGORAPJE command or use the GO CMDORAGTW command specify option 3 and press Enter The panel in Example 6 5 Change Oracle Prestart Parameters Panel appears Enter the new values and press Enter to continue Example 6 5 Change Oracle Prestart Parameters Panel Change Oracle Prestart Parameters System AS400A Type choices for prestart jobs press Enter Existing instance name ORACLE Name Start TCP IP JODS eek nime YES SAME YES NO Initial number of TCP IP jobs 2 1 1000 SAME TCP I
184. riction Oracle Call Interface programmatic limitation 2 4 OPEN LINKS parameter limiting the number of active databaselinks 7 3 operations date and time 9 18 ORA file members A 2 ORA LISTENER file contains parameter values for the listener A 2 making changes with ORA LISTENER CHG A 2 some network parameters not displayed on CHGORANET panel 6 6 ORA LISTENER CHOC file used to make changes to ORA LISTENER file A 2 ORA LISTENLOCK file ensuring only one instance of RUNORALSN program is running A 2 ORA SOLNET file contains network parameter values for Oracle Net A 2 some network parameters not displayed on CHGORANET panel 6 5 ORA 00947 error message not accompanied by DB2 400 error messages 10 3 ORA 1017 error invalid user ID or password 6 10 ORA 12154 connection error resolving connection problems 5 10 ORA 2068 connection error resolving connection problems 5 9 ORA 28500 error file notjournaled 4 8 resolving connection problems 5 10 ORA 28509 connection error resolving connection problems 5 9 ORA 28511 connection error resolving connection problems 5 9 ORA 28528 error example buffer too small for LONG data type 2 6 ORA 3114 connection error resolving connection problems 5 9 Oracle Call Interface OCI restriction programmatic limitation 2 4 Oracle Database 10g server post processing of SUBSTR SQL function 2 5 Oracle Database Server copying data from the DB2 400 server 7 9 database links
185. rite 2 4 Programmatic Limitations irisse iE iE REE eene ee en ene nennen nennen 2 4 Columns Defined with RAW Data sess nnns 2 5 GLOBAL NAMES Initialization Parameter eese eene enne 2 5 Precompiler Limitations ciere nieto dicet doeet cree e 2 5 Some SQL Functions Post processed esses 2 5 DB2 400 SQL Lita Si der n ni emitte dun iet 2 5 Oracle Bind Variables uus nene 2 5 CONNECT BY Is Not Supported reete teen 2 5 Oracle Incremental Materialized View Refresh sse 2 5 LONG Data Types unicidad iia 2 6 System Requirements 3 1 3 1 1 3 1 2 3 1 3 3 1 4 3 2 3 2 1 3 2 2 3 3 3 4 Hardware Requiteietits 22 ertet tete ai aE 3 1 PROCESSOR M E M M ts 3 1 una 3 1 CD ROM DEN 3 2 ate tede Me n ne Hb cb R 3 1 Disk Space cese ete parodia eda estt 3 1 Software Requirements iii tree eh eee Pte dis 3 2 Operating System Requirements sse nenne nennen enne 3 2 Oracle Integrating Server eee eene eene nnne nnne nennen 3 2 Documentation Requirements sssseseseeeeeeenenenn nne e nnn nnne nnne e nennen 3 2 Dist b tion KIt A ee eet ee e ER Ite Breq aree e dvs 3 2 Installing the Gateway 4 1 Gateway COeXISLence ineo nee Hot sere e ente ere tree met eren rre St Een 4 1 4 2 Ch ckli ts eie e ated tie 4 1 4 2 1 Preinstallatiori Checklist a Ee re reete i eh dink tete 4 1
186. ror messages do not occur For example if the gateway cannot be accessed because of an Oracle Net or gateway installation problem then the DB2 400 error message is not present in the received error message Another example of Oracle database error messages without DB2 400 error messages occurs when an INSERT statement attempts to insert data into a table but does not 10 2 Oracle Database Gateway for DB2 400 Installation and Users Guide Error Categories include values for all of the columns in the table This SOL statement causes an error message as follows SQL INSERT INTO EMP AS400 VALUES 9999 ERROR at line 1 ORA 00947 NOT ENOUGH VALUES The ORA 00947 message is not accompanied by DB2 400 error messages because the error is detected by the Oracle database The Oracle database obtains a description of the DB2 400 table before sending the INSERT statement to the gateway for processing This allows the server to detect when the INSERT statement is invalid 10 2 Contacting Oracle Support Services To maximize the effectiveness of your inquiries gather the following information before contacting Oracle Support Services OS 400 release number gateway release number found in the JobLog for a Gateway task Oracle Net release number found in the JobLog for a Gateway task Oracle database release number and platform 10 3 Error Categories Use the following error categories to describe the error docum
187. rrent server C 10 Oracle Database Gateway for DB2 400 Installation and User s Guide C 18 TABLE PRIVILEGES Grants on objects for which the user is the grantor grantee or owner or PUBLIC is the grantee Column Name Description GRANTEE Name of the user to whom access is granted OWNER Owner of the object TABLE NAME Name of the object GRANTOR Name of the user who performed the grant SELECT PRIV Permission to select from an object INSERT PRIV Permission to insert into an object DELETE PRIV Permission to delete from an object UPDATE PRIV Permission to update an object REFERENCES PRIV N A ALTER PRIV Permission to alter an object INDEX PRIV Permission to create or drop an index on an object CREATED Time stamp for the grant C 19 USER CATALOG Tables views synonyms and sequences that are owned by the user Column Name Description TABLE NAME Name of the object TABLE TYPE Type of object C 20 USER COL COMMENTS Comments on columns of user tables and views Column Name Description TABLE NAME Object name COLUMN NAMI Column name In COMMENTS Comments on the column C 21 USER CONS COLUMNS Information about columns in constraint definitions that are owned by the user Data Dictionary Views C 11 USER CONSTRAINTS Column Name Description OWNER CONSTRAINT NA
188. rring data into DB2 400 columns and no forcing of double byte characters to the single byte correspondences will take place when transferring data from DB2 400 columns Placing the string FORCE SB into ORADBMBOPT turns the feature ON Note that you should not use PassThrough to execute any SOL commands that contain graphic constants unless those constants conform fully with DB2 400 graphic constants DB2 400 graphic constants start with G G apostrophe or N N apostrophe and end with an apostrophe character The first character after the G or N must be a SHIFT OUT character and the character preceding the apostrophe at the end of the constant must be a SHIFT IN character Between the SHIFT OUT and SHIFT IN characters an even number of bytes must be present with each byte pair making up a single DBCS character Developing Applications 9 17 9 8 3 Performing Character String Operations The gateway performs all character string comparisons concatenations and sorts by using the data type of the referenced columns for example fixed or variable length The gateway determines the validity of character string values that are passed by applications and automatically converts character strings from one data type to another It also automatically converts between character strings and dates when necessary In contrast the Oracle Database 10g server processes character strings as
189. rsions to the numeric data type of the destination column such as BIGINT INTEGER SMALLINT PACKED DECIMAL and NUMERIC You have no control over the data type conversion and the conversion is independent of the data type of the destination column in the database For example if PRICE is an integer column of the PRODUCT table in DB2 400 then the update shown in this example inaccurately sets the price of an ice cream cone to 1 00 because DB2 400 automatically converts a double precision floating point to an integer UPDATE PRODUCT AS400 SET PRICE 1 50 WHERE PRODUCT NAME ICE CREAM CONE j Because PRICE isan integer in this example the DB2 400 server automatically converts the decimal data value of 1 50 to 1 The gateway returns values that are bound in the manner that is requested by the Oracle application For numbers the format is typically an Oracle number limited to a certain number of digits of precision by the application 9 9 SQL Functions One of the most important features of the Oracle Database Gateway product family is the ability to provide SOL transparency to the user and to the application programmer Foreign data store SOL functions can be categorized into three areas compatible translated and compensated 1 Compatible Compatible SOL functions have the same meaning and results on both Oracle and foreign data store Compatible SOL functions include b
190. s move the cursor to the value on the panel and press PF1 Enter the new values on the panels Before entering values for the DATABASE DOMAIN GATEWAY NATIONAL LANGUAGE RPC FETCH REBLOCKING and RPC FETCH SIZE gateway initialization parameters read the following notes Table 6 2 Initialization Parameter Notes Parameter Notes Default DATABASE DOMAIN If the value for the GLOBAL NAME parameter is set to WORLD TRUE in the Oracle database INIT ORA file then the value that you enter in this field must exactly match that specified for the DB DOMAIN parameter in the Oracle database INIT ORA file Gateway Language The GATEWAY NATIONAL LANGUAGE line nolonger AMERICAN AMERICA appears in the CHGORATUN screen but NLS NCHAR WE8EBCDIC37 actually still does exist for other reasons RPC FETCH REBLOCKING Ifthe RPC FETCH REBLOCKING parameter is set to YES and YES the default then the block size of the buffer RPC FETCH SIZE for SELECT statements is determined by the value of 40 000 theRPC FETCH SIZE parameter The recommended value for Oracle Database Gateway for DB2 400 is 40 000 TheRPC FETCH SIZE parameter defines the maximum number of bytes that are sent with each fetch between the gateway and the Oracle database Each fetch block may contain multiple rows of data V4 GRAPHIC compatibility Before deciding on a value fo
191. s Guide 10g Release 2 Gateways 1 2 2 1 Passthrough and Native DB2 400 SQL Native DB2 400 SQL can be passed through the gateway for running directly against DB2 400 This enables applications to send statements such as a DB2 400 CREATE TABLE to the gateway for execution on a target DB2 400 system 1 2 2 1 1 Data Definition Language Oracle applications can create tables in target data stores by using native data definition language DDL statements 1 2 2 1 2 Data Control Language You can issue native data control language DCL statements from an Oracle environment allowing central administration of user privileges and access levels for heterogeneous data stores Refer to Oracle Database Heterogeneous Connectivity Administrator s Guide for more information about DCL 1 2 3 Stored Procedures and Functions The gateway enables you to exploit both Oracle and non Oracle stored procedures and functions leveraging your investments in a distributed multi database environment Oracle stored procedures and functions can access and update multiple data stores easily with no special coding for heterogeneous data access 1 2 3 1 Oracle Stored Procedures and Functions Oracle stored procedures and functions enable you to access and update DB2 400 data using centralized business rules that are stored in the Oracle Database 10g server The use of Oracle stored procedures and functions can increase the database performance by minimizing network
192. s on page 2 1 Known Problems on page 2 2 Known Restrictions on page 2 3 2 1 Product Set The production components that are included on the product CD ROM are Oracle Database Gateway for DB2 400 10g Release 2 10 2 0 1 0 Oracle Net Release 10 2 0 1 0 2 2 Changes and Enhancements 2 2 1 Bugs The following changes and enhancements have been made to the Oracle Database Gateway for DB2 400 for this current release New DB2 Data Types Supported The DB2 400 large object data types namely binary large objects BLOBs character large objects CLOBs and graphic large objects DBCLOBs are now supported However these data types can only be used in SELECT statements At the current time data cannot be INSERTed into columns with these data types In addition the previous support in 10 2 0 1 0 for large objects via the use of locators is no longer available DB2 stored functions UDFs Supported DB2 400 User Defined Functions are now supported DB2 400 UDFs must be made known to Oracle before their use however The IFS file home myinst rdbms admin hs call names ora contains a line for each UDF Savepoints Supported Intermediate savepoints can now be set Data can be rolled back to these savepoints Bugs fixed in this release of the Oracle Database Gateway for DB2 400 are as follows Release Information 2 1 Bug 4959570 const CHAR fields in DD Views sometimes coming back as RAWs Bug
193. s that you use fully qualified stored function names when invoking DB2 400 stored functions from Oracle PL SQL and from SQL DML commands The parameter list of the stored functions When a DB2 400 stored function is defined the attributes of each entry in the argument list for that stored function are placed in the 0SYS2 SYSPARMS table The gateway uses this information to convert the data type of each argument to its corresponding PL SQL data type Refer to Converting DB2 400 Data types to Oracle Data types on page 9 11 for gateway data type conversion rules After the lookup of the description of the DB2 400 stored function is complete the gateway uses this information to execute the DB2 400 stored function 9 6 2 Considerations for Using Stored Functions with DB2 400 Following is a list of special considerations for use the procedural feature for stored functions with the Oracle Database Gateway for DB2 400 PL SQL records cannot be passed as parameters when invoking a DB2 400 stored function No matter the context in which the original function call was done that is either as a PL SQL function call or as a function call in a SOL DML statement the gateway executes the function as part of a SELECT That is it does SELECT func call func args FROM OTGDB2 DUAL Developing Applications 9 11 The gateway supports both GENERAL and GENERAL WITH NULLS linkage conventions of DB2 400 stored functions
194. s through the gateway Documentation Accessibility Our goal is to make Oracle products services and supporting documentation accessible with good usability to the disabled community To that end our documentation includes features that make information available to users of assistive technology This documentation is available in HTML format and contains markup to facilitate access by the disabled community Accessibility standards will continue to evolve over time and Oracle is actively engaged with other market leading technology vendors to address technical obstacles so that our documentation can be accessible to all of our customers For more information visit the Oracle Accessibility Program Web site at http www oracle com accessibility Accessibility of Code Examples in Documentation Screen readers may not always correctly read the code examples in this document The conventions for writing code require that closing braces should appear on an otherwise empty line however some screen readers may not always read a line of text that consists solely of a bracket or brace Accessibility of Links to External Web Sites in Documentation This documentation may contain links to Web sites of other companies or organizations that Oracle does not own or control Oracle neither evaluates nor makes any representations regarding the accessibility of these Web sites xi TTY Access to Oracle Support Services Oracle provides dedicated
195. se an AS 400 stored function DB2 400 user defined function in a SOL command without specifying that name in the above IFS file then you will get the Oracle error ORA 06571 function xxxxx does not guarantee not to update database Since it is assumed that AS 400 stored functions do not change the database gateway two phase commit processing does not result from calls made to these stored functions To effect gateway two phase commit processing changes must be made via a stored procedure or via an INSERT DELETE or UPDATE SOL command directed to an AS 400 object via the gateway Note The parameters to a DB2 400 user defined function are all of mode IN INOUT or OUT parameters are not allowed 9 6 Using DB2 400 Stored Functions with the Gateway The procedural feature of the gateway enables execution of DB2 400 native stored functions also called user defined functions or UDFs In other words the stored function are no longer defined in the Oracle Database but instead are defined to DB2 400 Oracle PL SQL may be used to directly call these functions within a PL SQL block Or the functions may be called as part of a SELECT INSERT DELETE or UPDATE SQL command If the stored function is referenced within a SOL command then the user must specify its name in the IFS file HOME MYINST RDBMS ADMIN CALL NAMES ORA where MYINST is your instance name If you attempt t
196. sing also applies to updates to DB2 400 that are being made within an Oracle stored procedure This means that the stored procedure can update a single instance of DB2 400 while also updating any number of Oracle Databases within a single transaction A call to a DB2 400 stored procedure counts as a write because as far as the Oracle Database Gateway for DB2 400 is concerned the gateway cannot tell if the stored procedure is actually changing any object in OS 400 So you cannot have a transaction that calls two DB2 400 stored procedures via two separate database links Correspondingly during a transaction you cannot call a DB2 400 stored procedure through one database link and also do an INSERT UPDATE or DELETE relative to DB2 400 using another database link even if the two database links resolve to the same OS 400 system 9 4 Using DB2 400 Stored Procedures with the Gateway The procedural feature of the gateway enables execution of native DB2 400 stored procedures In other words the stored procedure is no longer defined in the Oracle Database but instead is defined to DB2 400 Again standard Oracle PL SQL is used by the Oracle application to execute the DB2 400 stored procedure The gateway does not require special definitions in order to invoke the DB2 400 stored procedure After the stored procedure is defined to DB2 400 the gateway will be able to use the existing DB2 400 definition to execute the procedure Developing A
197. sm TCP IP 5 6 initialization parameters changing 6 6 GLOBAL NAMES 2 5 INIT ORA file GLOBAL NAMES initialization parameter 2 5 initialization parameter notes 6 7 INSERT clause Oracle server cannot post process 9 22 INSERT command copy data from DB2 400 server to Oracle Database server 7 9 not supported to copy tables 7 9 restriction 2 3 INSERT statement example for double byte character support 9 16 installation before installing pre installation steps 4 2 checking distribution kit 3 2 completion 4 6 mount product tape 4 3 required parameters 4 3 set required parameters 4 3 start process 4 3 starting the gateway subsystem 4 6 verify 4 5 INTEGER column performing numeric data type operations 9 20 Integrated File System system changes when installing the gateway 1 8 internal process communication 5 6 Internet support gateway advantages 1 4 isolation level journaling 4 5 1 4 6 using passthrough statements to create tables 9 13 isolation level journaling default setting 4 6 J job locks working with gateway job locks 8 4 job logs finding gateway version RUNORAGxxx job 47 JOBCTL special authority 4 3 jobs viewing 7 4 JOIN capability distributed capabilities 1 3 JOIN command accessing tables in multiple databases 9 2 journaling AS 400 files 4 7 cursor stability 4 6 definition 4 6 error messages 9 13 isolation level default 4 6 new tables 9 13 ORACLE2PC file 4 8 require
198. ssword is not valid if it expires for example then when an in doubt transaction occurs the Oracle Database alert log will most lik ely show an ORA 1017 invalid user ID or password error Use the CHGRECOPRF command to change the recovery profile parameters including recovery user ID and password 6 2 Setting Optional Parameters You can change the values of optional gateway parameters after the product is installed by using the gateway commands Three commonly changed parameters are Array block size inthe BLOCKSIZE data area Refer to Retrieving Data for more information Default character conversion in the ORARAW data area Refer to Data Conversion for more information Set gateway for READ ONLY for configuring the gateway with read only capabilities Refer to Read Only Gateway for more information 6 2 1 Retrieving Data The gateway can retrieve multiple rows from a table or view with a single fetch The gateway uses the BLOCKSIZE data area to determine the number of rows to retrieve These conditions apply for the BLOCKSIZE data area a Ifthe BLOCKSIZ E data area is set to 0 then no block retrieval is performed This is similar to setting BLOCKSIZE to 1 a Ifthe BLOCKSIZ E data area is set to n then the gateway retrieves n rows from DB2 400 in a single fetch where n is a value from 1 to 32767
199. sthe computer on which the database resides and the computer that runs the Oracle gateway On AS 400 only a gateway can be running The Oracle Database does not run on the AS 400 client task is the application using an Oracle Net driver to communicate with the Oracle Database server or gateway A server is also considered to be a client if it initiates a connection with another Oracle Database server or with an Oracle gateway protocol isa set of standards or rules governing the operation of a communication link driver isthe part of Oracle Net that supports a given network protocol or communication method network isa configuration of devices and software that are connected in order to interchange information 5 1 4 Oracle Net for AS 400 Architecture Oracle Net connections are established on the AS 400 through a listener A listener receives incoming connections from Oracle Net clients and starts or transfers to a job on the AS 400 system On the AS 400 the Oracle Net TNS listener is used for TCP IP Figure 5 1 shows communication between a client and the AS 400 Oracle Net on the AS 400 can accept TCP IP connections Figure 5 1 Oracle Net Communication to the OS 400 A AS 400 P 2 ee ee A Ee A Aa Sa M Oracle Net any Protocol YA Oracle Net any Protocol Oracle Net Listener Oracle Net TCP IP Prestarted Prestarted Client Job 1 Job n SoS ee Sees Soe Se aie
200. t point site of the distributed transaction Because the gateway is configured as commit and confirm it is always the commit point site regardless of the COMMIT POINT STRENGTH setting of any of the participating Oracle databases The gateway commits the local AS 400 unit of work after verifying that all Oracle databases in the transaction have successfully committed their work Because the gateway must drive the distributed transaction only one gateway can participate in an Oracle two phase commit transaction Two phase commit transactions are recorded in the ORACLE2PC physical file You must journal this file before issuing a distributed transaction to the AS 400 Refer to Step 3 Journal the ORACLE2PC File on page 4 8 for more information For additional information about the two phase commit process refer to the Oracle Database Administrator s Guide 7 6 2 1 Recovering Failed Transactions If a two phase commit transaction fails because the database connection is lost then the pending transaction is stored on the Oracle Database Every time a user attempts to login to the gateway via s specific database link the Oracle Server checks to see if there are any pending probably failed transactions that had previously used that same database link If so Oracle suspends the current gateway login and instead tells the Gateway to login in using the Recovery User Profile and Password and to perform some recovery operations After t
201. teway The command INSERT INTO gateway table8gateway SELECT FROM oracle table displays the following message ORA 2025 All tables in the SQL statement must be at the remote database Use the following SOL Plus syntax to copy data from your local Oracle Database to the DB2 400 server COPY FROM username password ORACLESRVR INSERT destination table8gateway USING query The next example selects all rows from the local Oracle EMP table and inserts them into the EMP table on the DB2 400 server COPY FROM SCOTT TIGER ORACLESRVR INSERT SCOTT EMP gateway USING SELECT FROM EMP Note Although the SOL Plus COPY command supports the APPEND CREATE INSERT and REPLACE options INSERT is the only option that is supported when copying to the DB2 400 server For more information about the COPY command refer to the SQL Plus User s Guide and Reference 7 9 Copying Data to Oracle Server from DB2 400 Server Use one of the following options to copy data from the DB2 400 server to the Oracle Database a Usethe CREATE TABLE command to copy data from the DB2 400 server to the Oracle database To create a table on your local database and to insert rows from a DB2 400 table use CREATE TABLE table name AS query The next example creates the table EMP in the local Oracle database and inserts the rows from the EMP table on the DB2 400 server CREAT
202. th the exception of some objects dealing with service programs and the items mentioned in parts 3 and 4 of this list The gateway can also be installed into a previously created DB2 400 SOL COLLECTION that contains objects of type FILE physical and logical files a journal of type JRN a journal receiver of type JRNRCV and a data dictionary object of type DTADCT The collection name will be the name of the library in which these objects are created by DB2 400 2 A user profile is created The user profile has the same name as the library that is created when you install the gateway 3 Asubdirectory entry is created in the home directory in the Integrated File System The subdirectory entry name will be the same as the instance name So if ORACLE is the instance name then a subdirectory node will be created at home ORACLE Further nodes will be created below this node Agent trace files will appear if requested in a subdirectory of the created node An initialization file will also appear in a subdirectory of this node 4 Alibrary named ORASRVLIB is created If the installation software finds that it must install the service programs that are found on the installation medium then a library with a name such as ORASRVnnnn where nnnn is a number will be created The relevant service programs and a file that is used for Globalization Support transactions will be placed in that library Additional files or members within
203. the AS 400 CHGUSRPRF command Refer to CHGRECOPRE Change Recovery Profile Parameters on page 6 9 Auxiliary storage uses the default of 1 or you can enter another numeric ID pool id Install Data turns on data dictionary installation The default is YES Dictionary Support Installation of the data dictionary view support lengthens the installation process Only one Database Gateway data dictionary is created for the entire AS 400 computer All Database Gateway instances use the same data dictionary The data dictionary is created in the library OTGDB2 After filling in the choices press Enter to continue 4 4 4 Step 4 Verify Installation The following steps verify installation of the gateway 4 4 4 1 Panel Verify Oracle Database Installation 4 4 4 2 Action Enter YES and press Enter to continue the installation process Example 4 3 Verify Oracle Install Panel Verify Oracle Install at V10 2 0 1 0 System AS400A Verify that you want to install the product Once this process has started it will create a library and other objects with the name of the instance specified below Enter YES to verify this operation before it is started New instance name ORACLE Installing the Gateway 4 5 enter YES to verify NO NO YES gt Bottom Fl Help F4 Prompt F9 Retrieve F12 Cancel 4 4 5 Step 5 Finish the Installation If you entered YES then a message is displayed on
204. the definition for the host name from Step 2 Define the Host Name on page 5 7 in the Configuring for TCP IP section contains the fully qualified name 5 8 Oracle Database Gateway for DB2 400 Installation and User s Guide Resolving Connection Problems Without a fully qualified host name domain name Oracle Net cannot resolve the address and you will be unable to access AS 400 data 5 6 2 ORA 28509 The following are some causes of the error 5 6 2 1 HS is missing from your tnsnames ora file HS isa parameter that you must specify in the tnsnames ora file or you will not be able to use version 10 of the gateway The correct syntax is shown at Connecting to the AS 400 through TCP IP on page 5 4 If HS is missing from the entry in your tnsnames ora file then some symptoms to look for are a hanging connection and errors ORA 2068 and ORA 3114 Search Metalink for Note 136294 1 which provides more details about this topic 5 6 2 2 HS Not in Correct Place HS is specified in tnsnames ora but it is not specified in the correct place or the number of parentheses is uneven an odd number due to missing or extra parentheses HS needs to be set up outside the CONNECT DATA specification If you misplace HS or if you do not have the correct parenthesis specification then you can get error ORA 28509 Other symptoms same as when HS is missing from your tnsnames ora file are hanging connection and errors ORA 2068 and
205. the screen approximately 5 minutes to 15 minutes later to inform you that the installation is finished Check the job log for error messages by using the following command DSPJOBLOG 4 4 6 Step 6 Start the Gateway Subsystem Enter the following command STRSBS instance name instance name where instance name is the name that is used in Step 3 Set Required Parameters on page 4 3 Installation of the gateway is complete 4 5 Postinstallation Steps If you have more than one instance of the gateway then repeat postinstallation steps for each instance Note that you need to install data dictionary support only once Refer to the table of installation choices in Step 3 Set Required Parameters for more information on data dictionary support Oracle recommends performing the following postinstallation steps 4 5 1 Journaling Journaling is the process of recording changes that are made to files on the AS 400 in order to ensure transactional consistency Every table on the AS 400 that is going to be changed by the gateway must be journaled Changes to a file are recorded by a JOURNAL in a JOURNAL RECEIVER Journaling is not required for gateways that are used for read only purposes By default the gateway runs under commitment control change CHG This means that a AS 400 files that are not changed by SQL operations do not need to be journaled Users can see pending changes that have been input by others before
206. tine 1 5 standard Oracle 1 5 port number changing the default gateway port number 5 7 configuring for listener 5 6 default used by Oracle Net listener 1521 5 4 TCP IP gateway installation panel 4 4 name choices of install panel 4 5 post installation configure Oracle Net 4 8 journal ORACLE2TC file 4 8 raise priority level 4 9 remove observability 4 8 steps 4 6 verify journaling 4 7 post processing of SQL functions 1 9 precompiler limitations 2 5 prestarted jobs changing parameters 6 6 Index 10 priority level raising gateway level 4 9 processing SQL 9 21 processor requirement 3 1 product set 2 1 protocols APPC LU6 2 1 3 commit confirm 1 6 DECnet 1 3 definition 5 2 SPX IPX 1 3 1 4 TCP IP 1 3 1 4 Q QCCSID default coded character set identifier 6 11 OCRTAUT parameter do not use value EXCLUDE 4 2 restriction 2 3 QSECOFR user class log on authority 4 2 QSYS2 SYSPARMS DB2 400 table 9 6 QSYS2 SYSPROCS DB2 400 table 9 6 qualified host name description 5 7 Option 12 of CFGTCP 5 10 queries distributed example 7 6 queries distributed performing 7 6 QUSRSYS library creating user profile that corresponds to installation library 1 8 R RAW columns restriction 2 4 data restrictions 2 5 reading DB2 400 database read and write access 9 2 read only capabilities on the gateway 6 11 READONLY parameter read only gateway option may provide improved performance and se
207. tion Instance name isa name from one to six characters long The default is ORACLE Instance password is the password for the user ID the Instance User Profile same as OS 400 User Profile that is created during installation The Instance User Profile user ID has the same value as the instance name 4 4 Oracle Database Gateway for DB2 400 Installation and Users Guide Installation Steps Table 4 1 Cont Name Choices on Install Panel Choice Name Description Prestart jobs prestarts the server jobs Use YES to prestart the server jobs or NO to prestart NO jobs The server jobs run the Gateway executable You can change how many jobs are prestarted by changing the value of the initial number of TCP IP jobs parameter by using the CHGORAPJE command For more information refer to CHGORAPJE Change Prestarted Job Parameters on page 6 6 TCP IP port number is 1521 which is the default If port 1521 is unavailable then enter a different port number Each instance requires a unique port number Each instance has its own LISTENER Recovery user is a profile name that you enter or use the default of the profile gateway instance name Recovery user is a password for the recovery user profile The default is the password gateway instance name Following installation this password must be kept in synchronization with the password for the Recovery User Profile which is changed by using
208. tion 12 Change TCP IP domain information The panel in Example 5 1 Change TCP IP Domain CHGTCPDMN is displayed Example 5 1 Change TCP IP Domain CHGTCPDMN Change TCP IP Domain CHGTCPDMN Type Choices press Enter Host name AS400A Domain name US ORACLE COM Domain search list DFT Host name search priority REMOTE REMOTE LOCAL SAME Domain name server Internet address 140 24 88 144 140 24 244 29 Bottom F3 Exit F4 Prompt F5 Refresh F10 Additional parameters F12 Cancel F11 How to use this dispay F24 More Keys 1 Enter your domain name if the Domain name field is empty Enter your host name if the Host name field is empty Press Enter to save your changes and return to the option list panel The changes take effect after you restart the gateway The host name and the domain name combine to form the qualified host name For example AS400A combines with US ORACLE COM to form AS400A US ORACLE COM as the qualified host name Oracle Net 5 7 5 4 4 Step 3 Verify the Host Name Verify that your host name is in your host name table by using the following AS 400 command PING host name domain name You can also use a PING command on the client side Oracle Database server to verify that it can communicate with the OS 400 Note The PING LOOPBACK command does not verify the host name 5 4 5 Step 4 Verify that the Listener is Started Use the Oracle Gateway Moni
209. to and from DB2 400 stored procedures can be null when they are passed using indicator variables Oracle Call Interface OCI or embedded PL SQL can be used in host programs to operate on indicator variables For information about writing DB2 400 stored procedures to use indicator variables refer to the IBM reference for DB2 400 To ensure commitment control integrity between the gateway and the stored procedure program you must create the ILE PGM object containing the DB2 400 stored procedure by using the activation group attribute CALLER If you do not use the CALLER attribute then stored procedures execute in a different activation group than the gateway If the activation group attribute was given as NEW then a forced COMMIT may occur when the activation group ends that is when the program object that is defined by the stored procedure ends For more information about activation groups refer to the IBM document for ILE concepts If the stored procedure does not change any file or any DB2 400 table on the AS 400 then the program object may be an OPM program object Otherwise the program object must be an ILE program object Failure to adhere to this may cause data to become unsynchronized in the case of a ROLLBACK or COMMIT that either may be planned by you or may be forced because of some failure Developing Applications 9 7 9 5 Using Oracle Stored Functions with the Gateway The gateway stored function support is
210. tools Introduction 1 5 1 2 5 SQL Plus Use SQL Plus for moving data between the databases This product gives you the ability to copy data from your department databases to corporate Oracle databases 1 2 6 Oracle Database 10g Server Technology and Tools The gateway is integrated into the Oracle Database 10g server technology which provides global query optimization transaction coordination for multi site transactions support for all Oracle Net configurations and so on Tools and applications that support the Oracle Database 10g server can be used to access heterogeneous data through the gateway 1 3 Two Phase Commit and Multisite Transactions The gateway can participate as a partner in multisite transactions and two phase commit How this occurs depends on the capabilities of the underlying data source meaning that the gateway can be implemented as any one of the following afulltwo phase commit partner a commit point site a asingle site update partner a aread only partner The deciding factors for the implementation of the gateway are the locking and transaction handling capabilities of your target database Oracle Database Gateway for DB2 400 by default is configured as a commit point site that is commit confirm protocol Optionally you can configure the gateway as read only if you choose to enforce read only capability through the gateway Other protocols are not supported Refer to Read Only Gateway on pag
211. tor to verify that the listener is started If itis necessary to start the listener then use the Oracle Gateway Monitor to do so or start the listener manually with the following command STRORALSN You can use the NETSTAT CNN command to verify that the port that is assigned to the LISTENER is in fact being used by the LISTENER job in a given gateway instance For more information about using the Oracle Gateway Monitor refer to Chapter 8 Administering the Gateway 5 5 TCP IP Connection Problems If you cannot access AS 400 data by using Oracle Net after configuring TCP IP then check to see whether the AS 400 is reachable by pinging the AS 400 from an Oracle Database server host from the operating system prompt or use a similar command from the shell prompt PING host name domain name where host name is the name that identifies the AS 400 If you cannot resolve your connection problem then refer to Message and Error Code Processing on page 10 1 for more information 5 6 Resolving Connection Problems You may experience the following error types AS 400 data is not reachable errors ORA 28509 ORA 28511 ORA 28500 ORA 12154 or a hanging condition 5 6 1 AS 400 Connection Problems The following suggestions may resolve your connection problems 5 6 1 1 The AS 400 is not reachable Review your Oracle Net configuration 5 6 1 2 The AS 400 is reachable but you cannot access AS 400 data Verify that
212. ty The gateway is shipped with IBM observability Removing observability reduces gateway program object size by approximately 60 percent If disk space is a concern and if you have no plans to upgrade to a higher version of OS 400 then Oracle recommends that you remove observability from the gateway To remove observability perform the following steps 1 Stop the gateway by entering ENDSBS instance name IMMED where instance name is the name that is given to the gateway when it is installed 4 8 Oracle Database Gateway for DB2 400 Installation and User s Guide Postinstallation Steps 2 Use the SBMJOB command to submit a batch job using the QSECOFR ID The SBMJOB syntax is SBMJOB CMD CALL instance name REMOVEOBS PARM instance name 4 5 7 Step 6 Raise the Gateway Priority Level The gateway subsystem and listener default to an AS 400 priority level of 50 during the installation process In many cases a priority level of 50 is conducive to performing the functions that are provided by the gateway However if the AS 400 is running at a high CPU capacity with several jobs competing for CPU allocation then you might need to raise the priority of the gateway subsystem and listener to meet your performance requirements Note The gateway runs as a batch job Raising the priority can impact the performance of other processes Raising the priority of the gateway to a priority higher than 20 is not recommended
213. variable See note below table BLOB LONG RAW CLOB LONG DBCLOB LONG GRAPHIC N CHAR 2 N 1 lt N lt 127 Maximum length of a DB2 400 VARCHAR2 2 N 127 lt N lt 16370 GRAPHIC column is 16370 graphic characters VARGRAPHIC N VARCHAR2 2 N 1 lt N lt 2000 Maximum length of a DB2 400 2000 lt N VARGRAPHIC LONG 2 N column is 16370 graphic characters LONG VARGRAPHIC N VARCHAR2 2 N 1 lt N lt 2000 Maximum length of a DB2 400 2000 lt N VARGRAPHIC LONG 2 N column is 16370 graphic characters FLOAT N FLOAT 21 1 N21 FLOAT N FLOAT 53 22 N 53 DECIMAL P S NUMBER P S Packed decimal in OS 400 NUMERIC P S NUMBER P S Zoned decimal in OS 400 BIGINT NUMBER 19 INTEGER NUMBER 10 SMALLINT NUMBER 5 Table Note TIMESTAMP appears in two rows in the table The data type that is used depends on the value in the ORATIMSTMP DataArea If the DataArea is missing then the CHAR 26 value is used If the DataArea exists and has value ON then the Oracle TIMESTAMP data type is used Developing Applications 9 15 Table Note If the column CCSID is 13488 UCS 2 and if the gateway NLS LANG specifies a single byte character set then replace 2 N in the Oracle Database column with N replace 127 with 255 and replace 2000 with 4000 If the column CCSID is 13488 and if the Oracle Database is expecting single byte data then VAR GRAPHICS are treated as VAR CHARs 9 8 1 DB2 400 GRAPHIC
214. way data dictionary views to see the objects in DB2 400 and to determine the authorized users of those objects Refer to Appendix C Data Dictionary Views for descriptions of DB2 400 catalog views 9 12 2 DB2 400 Special Registers You can access DB2 400 special registers by using the gateway During installation of the gateway a DB2 400 view is created in order to enable access to special registers For example to find out the primary authorization ID that is being used by the gateway the following command from your application SELECT CURRENT USER FROM OTGDB2 OTGREGISTER DB2400 where OTGDB2 is the default qualifier of the OTGREGISTER view and DB2400 is the name of a database link to the gateway Developing Applications 9 23 9 13 Oracle Developer Forms Compatibility You can use Oracle Developer Forms version 4 or higher to build applications that are stored in DB2 400 When you connect to a non Oracle data source with a gateway product you should be aware of four transaction processing options in Oracle Developer Forms These options include two block properties and two form module properties When designing the form set these options as described in the following table Table 9 3 Transaction Processing Options in Oracle Developer Forms Option Set Value To Key Mode block property Anything except Unique key Locking Mode block property Delayed Cursor Mode form module Close at commit Savepoi
215. word that is used to establish a session in the remote database This must be a valid OS 400 server password The password cannot be longer than ten characters tns name entry specifies the Oracle Net TNS connect descriptor that is used to identify the gateway subsystem This is identical to the CONNECT NAME that is found in entries in the tnsnames ora file Refer to Step 1 Add a TCP IP Connect Descriptor to tnsnames ora on page 5 4 7 1 3 Closing Database Links After being used a database link remains open for the duration of the gateway session If you want to close a database link during a session then you can do so with the ALTER SESSION CLOSE DATABASE LINK dblink statement 7 1 4 Accessing Data through Database Links DB2 400 tables views and synonyms that are available to the user ID that is specified inthe CONNECT TO clause can be accessed with the following syntax SELECT FROM table gateway Or SELECT FROM user table gateway The CONNECT TO user ID provides implicit qualification for unqualified tables For example SELECT FROM EMPGgateway resolves to SCOTT EMP on DB2 400 if the CONNECT TO user is SCOTT If no CONNECT TO statement is defined with the database link then the Oracle user ID that is using the database link is used as the implicit qualifier Oracle strongly recommends that your table or view specifications or both always be qualified with the table or view owner
216. y for DB2 400 Installation and Users Guide A Oracle Net Files and Members This appendix lists and defines the member names in the LOG ORA and TRC gateway library files This appendix contains the following sections File Members on page A 1 Agent Trace Files on page A 3 A 1 File Members Members are listed for the following file types LOG File Members a ORA File Members TRC File Members A 1 1 LOG File Members The LOG file contains the LISTENER member which is the default log output member for the Oracle Net TCP IP listener To display this member on the AS 400 use the following command WRKMBRPDM instance name LOG where instance name is the name that is used for the gateway when it is installed In the LOG file which is in the instance library the LISTENER member records information about the startup of the listener and also records information about every connection attempt that is made to the associated instance The file can get quite large over time and you should therefore occasionally clear out the LISTENER member entries by using the CLRPFM FILE command To issue the command you need to sign on to your iSeries computer with either the QSECOFR ID or the instance ID and then enter the following command CLRPFM FILE instance name LOG MBR LISTENER The LISTENER member can also be displayed using the WRKTRCLOG command The gateway instance librar
217. y should be in the library list when you use the WRKTRCLOG command Oracle Net Files and Members A 1 File Members A 1 2 ORA File Members The ORA file contains the following members A 1 2 1 LISTENER Member LISTENER contains the parameter values for the listener The values in this file should not be changed directly with an AS 400 editor The most commonly used parameters can be changed with the CHGORANET command All other values must be changed in the ORA LISTENER CHG file Refer to CHGORANET Change Network Parameters on page 6 5 for more information A 1 2 2 LISTENER CHG Member LISTENER CHG is used to make changes to the ORA LISTENER file Refer to CHGORANET Change Network Parameters on page 6 5 for more information A 1 2 3 LISTENLOCK Member LISTENLOCK is the ORA LISTENLOCK member It is used to ensure that only one instance of the RUNORALSN program is running The RUNORALSN program runs the TCP IP listener Do not modify this file A 1 2 4 SQLNET Member SOLNET contains the network parameter values for Oracle Net The values in this file should not be changed directly with an AS 400 editor The most commonly used parameters can be changed with the CHGORANET command All other values must be changed in the ORA SQLNET_CHG file Refer to CHGORANET Change Network Parameters on page 6 5 for more information A 1 2 5 SQLNET CHG Member SQLNET_CHG is used to make changes to the OR
218. y user ID itself is invalid it may have expired for example an ORA 1017 error invalid user ID or password will be inserted into the Oracle Database alert log The CHGRECOPRF command is used to change the recovery profile parameters including recovery user ID and password For more information about using CHGRECOPRE refer to CHGRECOPRE Change Recovery Profile Parameters on page 6 9 8 9 Streams Replication The Oracle Database Gateway for DB2 400 and Heterogeneous Services now support replication to DB2 400 by using Oracle Streams Oracle Streams is a rule based process that allows changes to an Oracle Database table to be captured and applied to an equivalent DB2 400 table based on user written rules Note Replication from an Oracle Database to DB2 400 is supported Replication from DB2 400 to an Oracle Database is not supported at this time Before setting up your Streams replication environment ensure that archivelog is enabled Note Streams will not work if archivelog is not enabled An example of a simple table replication follows First you should grant the necessary authorizations to your Streams admin user ID CONNECT SYS SYS PASSWORD AS SYSDBA GRANT CONNECT RESOURCE SELECT CATALOG ROLE TO strmadmin IDENTIFIED BY strmadminpw RANT EXECUTE RANT EXECUTE RANT EXECUTE RANT EXECUTE RANT EXECUTE RANT EXECUTE N DBMS APPLY ADM TO strmadmin N DBMS AQADM TO strmadmin N DBMS
219. ym N A C 9 ALL TAB COLUMNS Columns of all tables views and clusters that are accessible to the user Column Name Description OWNER TABLE NAME COLUMN NAMI El DATA TYPE DATA TYPE MOD DATA TYPE OWNER DATA LENGTH DATA PRECISION DATA SCALE NULLABLE COLUMN ID DEFAULT LENGTH DATA DEFAULT NUM DISTINCT LOW VALUE Owner of the table or view Table or view name Column name Data type of the column N A N A Maximum length of the column in bytes N A Digits to the right of decimal point in a number Asks if the column allow null values Value is n if there is a NOT NULL constraint on the column or if the column is part of a PRIMARY key Sequence number of the column as created N A N A Number of distinct values in each column of the table Second lowest value for tables with more than three TOWS These statistics are expressed in hexadecimal notation for the internal representation of the first 32 bytes of the values C 6 Oracle Database Gateway for DB2 400 Installation and Users Guide Column Name Description HIGH VALUE DENSITY E NUM_NULLS NUM_BUCKI LAST_ANA ETS YZED SAMPLE_SIZE CHARACTE R_SET_NAME CHAR_COL_DECL_LENGTH GLOBAL_STATS USER_STATS AVG_COL EN CHAR_LENGTH CHAR_USE D V80_FMT

Oracle Database Gateway for DB2 Installation and User's Guide

Contents

Download Pdf Manuals

Related Search

Related Contents