Oracle for IBM OS/390 (MVS) Mainframes

Please help your system administrator to save OS/390 from dying out. Get Oracle on your mainframe as quickly as possible.

SQL*Forms V4 and above will never be ported to MVS. All current SQL*Forms users MUST convert to CICS/COBOL or migrate their Forms to a different platform like Unix or MS-Windows.

UPDATE: Oracle 9iAS and IBM's Web Sphere can run 9i Forms Servlet under IBM z/os Linux. So it's back! And better then ever if you include mod_plsql functionality, etc.

For more information regarding Oracle Forms, see the Forms 3.0 and Forms 4.5 and above FAQ's.

1. With the "Oracle Access Manager for CICS" product you can write 3GL (eg COBOL, C, etc.) applications that execute from CICS.
   
2. SQL*Forms V2.3 and V3.0 can execute from CICS but Oracle de-support SQL*Forms on MVS effective from 1 January 1998.
   
3. Transaction synchronization can be controlled by CICS (EXEC CICS SYNCPOINT). This means that multiple data sources like DB/2, IMS, as well as Oracle on MVS and non-MVS platforms can be updated or rolled back as a single transaction.
   
4. Oracle Access Manager for CICS replaces CICS-Attach. With Access Manager it is no longer required to have an Oracle database on MVS.
   
5. SQL*Forms is conversational and thus uses lots of memory. CICS V4 is better optimized to run conversational transactions than its predecessors because it manages memory better.
   
6. Use MPMTCB=NO (in the thread definition table) for pseudo-conversational transactions if there is less than 70 concurrently active users in one CICS.
   
7. Always use MPMTCB=YES for conversational transactions written in SQL*Forms or PRO*Cobol.
   
8. Setting PROTECT=NO will use more CPU for thread creation but saves memory and can prevent SOS (Short on Storage).
   
9. Use LE/370 (IBM's Language Environment) for user exits. It will move 11K per user from the UDSA to the EUDSA above the line.
   
10. Use the CICS phase-in facility to introduce new or updated programs. Newcopy will not work if a program is in service.
    
11. Before Oracle 7.1 you had to compile SQL*Forms user exits with the DBMS=V6 precompiler option.
    
12. Oracle claims CICS ATTACH 2.1 is +-20% faster than its predecessors (V7 mode).
    
13. CICS generally thrashes when 90+% (sometimes even at 70%) of a single CPU is busy (DFHKCP is single threaded).
    
14. CICS should have higher priority than the database (MPM).
    
15. Use products like TMON and OMEGAMON to gather transaction statistics.
    

1. Spread your data files across discs and channels. Stripe table data with the ALTER TABLE x ALLOCATE EXTENT (DATAFILE y); command.
   
2. The I/O rate on rollback segments is extremely high - allocate it on fast devices.
   
3. Log files determine COMMIT time performance since all transactions synchronize with it. Allocate log files on fast devices.
   
4. Use DASD Fast Write for redo log files. Just make sure nobody will try to flush the disk caches :-)
   
5. DFSMS is not supported by Oracle, you can't create storage groups to separate slow and fast devices.
   
6. Full table scans can hurt performance. V$SESSTAT can be used to monitor full table scans.
   
7. Look-out for DASD hot spots (RMF stats, V$FILESTAT).
   
8. 20-30 ms response for 3380's vs 8-18 ms for 3390's is typical.
   

1. Use REGION=0M for MPM and control memory usage by setting INIT.ORA parameters.
   
2. Use RMF stats: High UIC count, high MIG and low DPR is good.
   
3. Oracle can't use hyper or dataspaces like DB/2.
   
4. Put frequently used re-entrant modules like RTLDRV in PLPA and EPLPA.
   

1. CICS should have higher priority than the database (MPM).
   
2. TNS should have higher priority than CICS and MPM.
   
3. Use the SRM parameter MTTW for MPM regions (before MVS 5). Databases that do the most I/O will get the highest priority.
   
4. Slow running batch jobs can cause lockouts on OLTP service, increase batch priority or use less concurrent batch.
   
5. Runaway tasks can be stopped with the RESOURCE LIMITER but it will also kill operations like index creation, etc.
   

1. To increase I/O throughput, experiment with:
   
   • _DB_BLOCK_WRITE_BATCH=32 (note the underscore),
   • DB_BLOCK_CHECKPOINT_BATCH=32, and
   • DB_FILE_MULTIBLOCK_READ_COUNT=32.
2. Set CHECKPOINT_PROCESS=TRUE for large databases to optimize checkpointing.
   
3. Increase TRANSACTIONS_PER_ROLLBACK_SEGMENT if you experience rollback segment header waits.
   
4. LOG_CHECKPOINT_INTERVAL should be large to minimize checkpointing but will slow down database startup (recovery takes longer than to re-generate changes).
   
5. The default SORT_AREA_SIZE is way to small increase it to at least 0.5 Meg.
   
6. Make sure PRE_PAGE_SGA=NO (the default).
   
7. TIMED_STATISTICS=YES will incur +-10% CPU overhead but can provide valuable tuning info.
   
8. DB_BLOCK_BUFFERS between 5000 and 10000 works best on MVS.
   
9. Use a really big SHARED_POOL_SIZE - 20 Meg or higher per instance - use DBMS_SHARED_POOL.SIZES() to monitor large objects. Fragmentation will be severe. Oracle doesn't cleanup/compress this area.
   
10. The DYNWORK= parameter in MPMPARMS should be at least 12 but might cause inadequate resource consumption when to high (CPU idle with lots of work waiting).
    
11. If your network is unstable and you experience lots of TX lock requests in V$LOCK, increase CLEANUP_ROLLBACK_ENTRIES.
    

1. A good design is still key. Index appropriately and watch row chaining.
   
2. Monitor V$SESSION_WAIT regularly to identify wait conditions. If the SEQ# column stops changing, that event is stuck.
   
3. Monitor locking and latching (V$LOCK, V$LATCH, V$LATCHHOLDER, etc.).
   
4. SQLDBA still needs a lot of work. Server Manager only supports line mode operations on MVS.
   
5. Create enough rollback segments (say #active_transactions/4, but less than 50) in a separate tablespace, cache it and watch WAITS and SHRINKS.
   
6. Use a temporary tablespace with PCTINCREASE=0 and large INITIAL=NEXT extents (but a multiple of SORT_AREA_SIZE).
   
7. DB links will dedicate database workers if you don't use TCP/IP! Big DYNWORK= parameter implications.
   
8. Oracle doesn't support mirrored databases so DSS and OLTP must run on the same database (it's also not possible to have a hot standby database).
   
9. 2PC (two phase commit) is slow, can even cause read blocks (monitor DBA_2PC_PENDING).
   
10. Tune all SQL statements regularly, use EXPLAIN PLAN, TKPROF, DELPHI Monitor, etc.
    
11. Use Oracle7 performance features like HASHING, enforced INTEGRITY constraints, etc. But Oracle still doesn't support CLUSTERED INDEXES.
    
12. Use TRUNCATE instead of DROP and re-CREATE.
    
13. Bad optimizer execution plans can be extremely expensive, data HISTOGRAM's is still not supported by Oracle.
    
14. Be careful with the COST based optimizer. It might be slower and will need manual tuning (hinting). ANALYZE regularly! Consider switching from RULE to COST when Oracle starts to use the cost based optimizer for their dictionary.
    
15. Write a data API (stored proc's, packages) on tables to avoid user SQL being issued against them. This will reduce network I/O and protect data integrity.
    
16. A good SQL coding standard will optimize the use of the shared pool.
    

1. Assign a TCP/IP port to each database. You can't use one port to listen to all your databases like on Unix systems.
2. If you make use of database links or any distributed functionality like snapshots, Oracle will dedicate worker tasks unless you go through the TCP/IP drivers.
   
3. Some of the more popular protocols are: TCP/IP (T: driver); VTAM (B: driver); LU6.2 (L: driver); cross memory (Z: and M: drivers); etc.
   
4. The M: cross memory driver is supported for compatibility only. Convert to Z:
5. SQL*NET V2 is faster (but more difficult to configure) than V1 and uses a separate TNS address space.
   
6. TNS should have higher priority than CICS and MPM.
   

1. Use the Oracle Parallel Query Option (available from Oracle7.1) to execute batch or decision support queries (but only if you have sufficient CPU and I/O resources).
   
2. The Oracle Parallel Server Option for MVS is production! It require an IBM 9674 (coupling facility) to handle inter instance locking.
   
3. Note that in Oracle7 the Parallel Server Option does not support fine grain locking on OS/390. With Oracle8, however, the DLM no longer relies on a platform specific lock manager implementation, so fine grain locks are supported for all platforms.

You will also need to design your application in such a way that the DB2 SQL's and Oracle SQL's are in different source modules that are prepared differently.

1. Oracle's Transparent Gateway for DB2 allows DB2 to completely participate in a SQL*Net network.

2. You can access (read/write) DB2 data through normal database links.

3. If you have the distributed option, you can even push data into DB2 via a database link.

Some Oracle database utilities, like sqlplus and tnsping, are also available from Unix Services. As one one can scroll the screen up and sown from OMVS, utilities like sqlplus are much easier to use from the Unix side.

For more details, refer to the Appendix in the Oracle for OS/390 User's Guide.

1. Remember NOT to format the dumps
   
2. Ship only 3480/3490 cartridges to Oracle
3. The high level qualifier of your MVS data sets that are associated with a bug should be BUG$.
   
4. The middle level qualifier associated with the data set should be Bxxxxxx, where xxxxxx is the bug number.
   
5. The rest of the data set qualifiers are your choice, but try to make them meaningful for developments sake.
   
6. Give a brief description of what each data set contains. A very brief example would be (assuming the bug number was 123456):
   LOAD THE FOLLOWING DATA SETS:
   1. BUG$.B123456.DUMP - AN SVC DUMP
   2. BUG$.B123456.TRACE - A GTF TRACE
   
7. Label tapes clearly with the following information: Product, Volser, Operating System (MVS or VM), Sequence (i.e. 1/2), Userid of sender and Date.

1. Numerous S0C4 abends in 7.3.2 of Oracle on MVS:

   Problem: Customer reports of S0C4 abends in ORACODE +64510 when executing PL/SQL scripts. There are a number of other S0C4 abends which this Kernel also resolves.

   Solution: Apply fix 467186 or any of the Kernels that superceed this kernel. All sites which are running applications on MVS must use this kernel. Currently the latest Kernel for 7.3.2 is 551581.

   References: Bug:467186 Bug:551581

2. Archive jobs start failing with an S0C4 after MVS has been Upgraded or serviced:

   Problem: After upgrading to OS/390 R1.2 or servicing MVS customers start getting S0C4 abends with their archive jobs. These abends happen almost immediately after the job has been submitted. The users will find that PTF UW34547 has been applied. Solution: Apply Zap from bug:549853. Two version exist one for 7.2.3 and one for 7.3.2 and 7.3.3.

   References: Bug:549853

3. Media Manager Failure on MMCALL from Oracle during startup.

   Problem: This problem happens either when the system has been upgraded or to be more precise when SMS has been upgraded to 1.3. It only affects versions of Oracle prior to 7.3.2. An SVC dump will be created with the title media Manager failure on MMCALL from Oracle.

   Solution: Apply zap from bug:337774.

   References: Bug:337774

4. SQLNET connections hang around after client session has ended. Also S0C4 abends in TNS after client sessions have terminated, the JOBLOG will show a message saying TXM-11661E with an RC of -27 or -36 or -47.

   Problem: This problem is exhibited in many ways. Basically TNS is not cleaning up client connections correctly after they terminate in unexpected ways. This could be if a user powers off their PC or a real error has been encountered, thus the TXM-11661E message shortly before the failure. This is for IBM's TCPIP only.

   Solution: Apply the fix from bug:457151 which is a new TNTI kernel. Versions of this fix exist for 7.1.6, 7.2.3 and 7.3.2. This is fixed in 7.3.3.

   References: Bug:457151

5. S0C3 abend in TNSMAIN.

   Problem: TNS listener abends sporadically with an S0C3 abend. This is caused by internal trace routines using an incorrect trace buffer record length.

   Solution: Apply the zap from bug:350088. This is fixed in 7.3.2 and above.

   References: Bug:350088

6. S0C4 U0055 and other abends in Oracle address space when using SNS/TCPaccess.

   Problem: The SNSDRV shipped with 7.1.6 and 7.2.3 causes random overlays under heavy load when worker tasks are in use.

   Solution: Apply fix from bug:388040 for 7.2.3 and bug:388037 for 7.1.6. These fixes perform relinks of the ORACODE module. This MUST be done with IEWL (HEWLKED). If the re-link is done with HEWL (IEWBLINK) then the module will abend with an S0C4 at startup. This is fixed in 7.3.2 and above.

   References: Bug:388040 Bug:388037

7. TXM11653E and TXM11654E messages when starting up a connection to IBM TCPIP from TNS.

   Problem: When starting up the connection between TNS and IBM's TCPIP when the TCPIP jobname is NOT TCPIP then you will get the following error message: TXM11653E VMCF ERROR FUCTION=0002, RC=5 followed by TXM11654E VMCF ERROR ISSUING TCP CALL=101 for connection xxx

   Solution: Run the job Oracle_hlq.NET2.SAMPLIB(TCPNAME) after it has been modified so that TCPIP becomes the Jobname of the started task running IBM's TCPIP.

   References: None.

8. U0133 abend when trying to mount a database under OS/390 R1.2

   Problem: The customer experiences a U0133 abend when they try to mount the database. This happens with a few datafiles. The level of MVS they are running is OS/390 Release 1.2 and above. The UCBs for all the devices that give this are are now above the line.

   Solution: Starting with OS/390 Release 1.2 devices that had been defined in HCD with LOC=ANY are placed above the 16M line. Prior to this release these UCBs were placed below the 16M line regardless of what is specified in HCD. See Bug:484649 for zaps which will fix this problem on 7.1.6, 7.2.3, 7.3.2 and 7.3.3.

   References: Bug:484649

9. Getting U1511 abend when starting up Oracle after servicing it.

   Problem: After receiving fix bug:500149 or any other kernels built on top of this kernel (eg bug:531425, bug:551585) the customer gets a U1511 abend when mounting the database.

   Solution: The customer is not running DF/SMS 1.3 and thus does not need fix 337774 applied to their system. In fact they need this fix removed. There is a zap under 337774 which has been written to remove 337774x

   References: Bug:500149 Bug:531425 Bug:551585 Bug:337774

10. ASRA abend 0C4 and a number of other problems with Access Manager For CICS when using SQLNET on Oracle 7.2.3.

    Problem: General ASRA abends and other problems using Access manager for CICS version 3.1.1.

    Solution: Apply fix bug:393240 which are replacement modules ORACICS and ORACICN. Zaps for bug:481500 and bug:470167 should be applied.

    References: Bug:393240 bug:481500 bug:470167

11. AEY9 Abend when trying to use Oracle via Access Manager for CICS.

    Problem: An AEY9 abend occurs when a program is called that uses access manager for CICS to access an Oracle database.

    Solution: The adapter named in the SQLCICS stub linked into the application has not been started. The customer needs to start the transaction by doing an ORA2 START MOD(name). The reason why this happens is because Access manager for CICS has been written as a task related user exit which uses and external resouce manager.

    References: None.

12. Access manager for CICS failing to start.

    Problem: If language modules Documented as LX***** are missing then when access manager for CICS starts up it can get S0C4 abends and ORA-3106 messages being issued and the adapter fails to start.

    Solution: The only way to currently resolve this issue is to first check that all CEDA DEFINEs are done in step 11.1 in chapter on Configuring Oracle Access manager for CICS of the Oracle for MVS installation guide. Next run a CICS Auxtrace when starting up the adapter. The CICS auxtrace should show a load failure with a PGM NOT FOUND or something similar for a module beginning with LX. On versions prior to 7.3.2 you could also use CEDF to trace the program when it attempts to call a language module.

    References: None.

13. Configuring Remote adapters for Access Manager for CICS

    Problem: General configuration problems with Acess manager.

    Solution:

    The dataset containing the remote adapter defintion MUST be Fixed block and have an LRECL of 80. There must be NO imbedded blanks between the parameters. You are allowed to put spaces at the beginning and end of the lines only. To check it has worked properly look in the assembled output and you should see the adapter definition.

    References: none.

14. Client connections to MVS exhibit slow performance or generate trace files on MVS with error messages in them like invalid userid and password. This only happens with 7.3.2 of Oracle.

    Problem: The reason for this problem is that the client system does not understand code page 1047. With Oracle on MVS 7.3.2 the default character set was changed to 1047 from 37C. Unfortunately most client platforms currently do not understand this code page which either means that the server needs to do all the NLS translation or trace files will be generated and the connections will not work properly.

    Solution: Either recreate the database with character set 37C or ask for the client platform to understand the codepage WE8EBCDIC1047

    References: None.

15. Set WKR_IDLE_TIME = 0 in MPMPARM for 7.3.3.1

    Problem: In order to get the best performance out of Oracle 7.3.3.1 the WKR_IDLE_TIME should be set to 0 in MPMPARM. By default this is set to 600 but for most user configurations this should be set to 0.

16. Getting ORA-4120 when connecting to non 7.3.3 systems on MVS via cross memory:

    Problem: When using SQLPLUS or something similar from the 7.3.3 CMDLOAD to connect via cross memory to a non 7.3.3 database, you may see message: ORA-4120 USER TABLE ENTRY COULD NOT BE LOCATED. and the connection will not work correctly.

    Solution: 7.3.3 introduced a new fast cross memory driver (W driver) which is made the default for clients. As all databases below 7.3.3 do not understand this driver you will get this message. The solution is to either use the correct version of CMDLOAD with the correct level of database or when connecting to the non 7.3.3 database specify the driver (eg @Z:UK02). The other solution is to apply zap from bug:507364 which changes the default driver back to the version for 7.3.2.

    References: bug:507364

Always test scripts carefully before using them!!!

Oracle JCL Utilities:

1. Sample SQL*Plus batch job
   
2. Sample database export job
   
3. Sample database import job
   
4. Sample SQL*Loader job
   
5. Sample RMAN job
   
6. Hot backup an Oracle Database
   
7. Build a CICS Access Manager adaptor
   
8. Sample CICS Access Manager adaptor
   

   General JCL Scripts:

9. Download Oracle Install scripts from distribution media
   
10. Rename an Oracle database file
    
11. Move an Oracle database file
    
12. Delete a database file
    
13. Create and format a new Oracle database file
    
14. Copy one controlfile to another
    
15. Alter sharing options for a file
    
16. RMF report on DASD utilization
    
17. Delete a HSM managed file
    
18. Sample RMAN (Recovery Manager) JCL
    
19. Dump Oracle database files to tape
    
20. Restore Oracle database file from tape
    
21. Copy dumps to tape
    
22. Allocate a tape/kast in the catalog
    
23. Run Unix System Services commands from JCL
    

    Oracle Parameter Files:

24. Sample MPMTNS parameter file
    
25. Sample MPMPARM parameter file
    
26. Sample ORA$FNA table (File Name and Attributes)
    

    Console Commands:

27. MPM console commands
    
28. TNS console commands
    
29. SLIP trap to prevent OC4 dumps in MPM
    
30. Make a dataset APF authorized
    
31. Define a new MVS Subsystem from the console
    
32. Useful Console Display commands
    

    Rexx command procs

33. Rexx exec to start interactive SQL*Plus
    
34. Rexx exec to start interactive Svrmgrl
    

• APAR (authorized program analysis reports)
  If one needs to correct a serious problem that occurs on a system before a PTF is ready, IBM Support can supply you with an APAR on request.

• MPM
  Multi Processing Monitor (MPM) is the Oracle address space running to support an Oracle instance.

• PDS (Partition Data Set)
  A library to store members (files) in.

• PTF (program temporary fix)
  When IBM Support correct a problem they will supply their customers a tested PTF. Although you may not have the problem, it is wise to install the PTF on your system for preventive reasons.

Oracle for OS/390 links:

1. Oracle's OS/390 Home Page
   
2. Oracle/MVS SIG Home Page
   
3. DELPHI Real-time Performance Monitor for Oracle/MVS - Do they still support it?
   

Oracle vs. DB2 Links:

1. Oracle from a DB/2 perspective
   

Other OS/390 links:

1. CBT Tape - Great page for Mainframe resources
   
2. Eric Loriaux's MVS/ESA home page - The Mainframe meeting point
   
3. OS/390 Unix Tools - See sample Oracle utilities
   
4. MVSHelp.com - The resource site for Mainframe Programmers
   
5. MainFrames.com
   
6. IBM System/390 home page
   
7. OS/390 Internet library - MVS manuals on-line!!!
   
8. IBM Red Books
   
9. IBMLink