Index   Search   Add FAQ   Ask Question  

Oracle for IBM OS/390 (MVS) Mainframes

$Date: 08-Mar-2000 $
$Revision: 2.50 $
$Author: Nico Booyse and Frank Naudé$

The mainframe is alive and well and definitely a good platform to run a good database like Oracle on.

Yes, the Dinosaurs are back, and they are pissed!!!

Topics

  • General myths about Oracle on MVS
  • How can I convince my System Administrator to allow Oracle on OS/390
  • What happened to SQL*Forms on MVS?
  • CICS Issues
  • DASD Issues
  • Memory Issues
  • Priority Scheduling
  • Initialization Parameters
  • Database Performance
  • SQL*Net Issues
  • Move to Parallel Operations
  • How does one connect Oracle to DB2?
  • Can one access Oracle from OpenEdition Unix Services?
  • Submitting dumps to Oracle
  • Common MVS/Oracle problems with workarounds
  • JCL scripts and sample parameter files
  • Understanding OS/390 Jargon
  • Oracle OS/390 related information on the Net

  • Back to Oracle FAQ Index

    General myths about Oracle on OS/390

    1. Myth: Oracle was developed on Unix and is therefore not optimized for OS/390.
      Fact: Oracle is always on the cutting edge in exploiting platform specific features in their operating system ports. Oracle on OS/390 makes extensive use of OS/390 facilities like RACF, VSAM, SMF, Media Manager, Cross Memory Facility, Coupling Facility, etc.

    2. Myth: Oracle on OS/390 is always released way behind the rest of the Unix world.
      Fact: Oracle never releases the first Oracle release on OS/390 as OS/390 people come to expect a slower software change rate and less buggy software. The current release for MVS is Oracle8i Release 8.1.5 (an update to V8.1.5.1.1 is available) just like on most Unix platforms.

    3. Myth: DB/2 is the only viable database on MVS.
      Fact: This is an old IBM toilet trained belief that even ADABAS users suffer from. Oracle is more feature rich than DB/2 and had row level locking years before its competitors.
  • Back to top of file

  • How can I convince my System Administrator to allow Oracle on OS/390

    MVS system administrators are often extremely negative against running Oracle on MVS. One should explain to them that it definitely not a thread to them. Running Oracle on MVS will draw more people to the mainframe, creating more work and demand for OS/390 systems.

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

  • Back to top of file

  • What happened to SQL*Forms on MVS?

    Oracle desupported both SQL*Forms V2.3 and V3.0 for MVS effective from 1 January 1998. Nevertheless, many large companies still run Forms on their mainframes.

    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.

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

  • Back to top of file

  • CICS Issues

    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.
  • Back to top of file

  • DASD Issues

    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.
  • Back to top of file

  • Memory Issues

    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.
  • Back to top of file

  • Priority Scheduling

    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.
  • Back to top of file

  • Initialization Parameters

    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.
  • Back to top of file

  • Database Performance

    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.
  • Back to top of file

  • SQL*Net Issues

    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.
  • Back to top of file

  • Move to Parallel Operations

    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.
  • Back to top of file

  • How does one connect Oracle and DB/2?

    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.

  • Back to top of file

  • Can one access Oracle from OpenEdition Unix Services?

    With Oracle7 for MVS Version 7.1.6 or higher, Oracle applications written in C or Assembler can be compiled and executed from an OS/390 UNIX environment. A UNIX application running under OS/390 UNIX Services can access the Oracle database on OS/390 (or anyware else).

    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.

  • Back to top of file

  • Submitting dumps to Oracle

    Tips to make the Oracle developers life easier AND hopefully get your problem resolved quicker:
    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.
  • Back to top of file

  • Common MVS/Oracle problems with workarounds

    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 a 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

  • Back to top of file

  • JCL scripts and sample parameter files

      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. Hot backup an Oracle Database
    6. Build a CICS Access Manager adaptor
    7. Sample CICS Access Manager adaptor

      General JCL Scripts:

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

      Oracle Parameter Files:

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

      Console Commands:

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

      Rexx command procs

    31. Rexx exec to start interactive SQL*Plus
    32. Rexx exec to start interactive Svrmgrl

  • Back to top of file

  • Understanding OS/390 Jargon

    OS/390 jargon can sometimes be extremely intimidating. Here are some common definitions that will help you to get up to speed with the OS/390 operating system:
  • Back to top of file

  • Other Oracle OS/390 related information on the Net

    Oracle for OS/390 links:
    1. Oracle's OS/390 Home Page
    2. Oracle OS/390 Products and Patches
    3. Oracle/MVS SIG Home Page
    4. Oracle/MVS Parallel Server Option for SYSPLEX
    5. DELPHI Real-time Performance Monitor for Oracle/MVS - Do they sill support it?
    Oracle vs. DB2 Links:
    1. Oracle from a DB/2 perspective
    2. Migrating an Application from Oracle to DB2 - We know you would not want to, so it is save to list this site

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

  • Back to top of file

  • General: Home | Index | Preamble | Glossary | OraCorp | Papers | Fun | News | Events | Y2000 | Books | Links | Forums
    Products: SQL | Plus | Loader | PL/SQL | PreComp | OPO | OMO | OO4OLE | DBA | PQO | PSO | OCO | Net | ODBC | WebServer | Des2k | Dev2k
    Systems: MVS | Unix | Windows | WindowsNT | NetWare | VMS