IBM Support

SI67347 - OSP-DB-LOOP SQL GLOBAL VARIABLE CAUSES LOOPING OR MSGSQL7009

PTF Cover Letter


PTF ( Program Temporary Fixes ) Cover letter


Order this fix

Abstract

OSP-DB-LOOP SQL GLOBAL VARIABLE CAUSES LOOPING OR MSGSQL7009


Pre/Co-Requisite PTF / Fix List

REQ  LICENSED      PTF/FIX  LEVEL

TYPE PROGRAM  REL  NUMBER   MIN/MAX  OPTION
---- -------- ---  -------  -------  ------
CO   5770SS1  710  SI64212   00/00    0000
CO   5770SS1  710  SI62812   00/00    0000
CO   5770SS1  710  SI58952   00/00    0000
CO   5770SS1  710  SI58007   00/00    0000
CO   5770SS1  710  SI58009   00/00    0000
CO   5770SS1  710  SI52312   00/00    0000
CO   5770SS1  710  SI48018   00/00    0000
CO   5770SS1  710  SI42927   00/00    0000
CO   5770SS1  710  SI40609   00/00    0000
CO   5770SS1  710  SI40610   00/00    0000
CO   5770SS1  710  SI40611   00/00    0000
CO   5770SS1  710  SI39900   00/00    0000
CO   5770SS1  710  SI39903   00/00    0000
CO   5770SS1  710  SI39902   00/00    0000
CO   5770SS1  710  SI39905   00/00    0000
CO   5770SS1  710  SI44208   00/00    0000



NOTICE:
-------
Application of this PTF may disable or render ineffective programs that
use system memory addresses not generated by the IBM translator,
including programs that circumvent control technology designed to limit
interactive capacity to purchased levels.  This PTF may be a prerequisite
for future PTFs.  By applying this PTF you authorize and agree to the
foregoing.

This PTF is subject to the terms of the license agreement which
accompanied, or was contained in, the Program for which you are obtaining
the PTF.  You are not authorized to install or use the PTF except as part
of a Program for which you have a valid Proof of Entitlement.

SUBJECT TO ANY WARRANTIES WHICH CAN NOT BE EXCLUDED OR EXCEPT AS EXPLICITLY
AGREED TO IN THE APPLICABLE LICENSE AGREEMENT OR AN APPLICABLE SUPPORT
AGREEMENT, IBM MAKES NO WARRANTIES OR CONDITIONS EITHER EXPRESS OR IMPLIED,
INCLUDING BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OR CONDITIONS OF
MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, AND NON INFRINGEMENT,
REGARDING THE PTF.

The applicable license agreement may have been provided to you in printed
form and/or may be viewed using the Work with Software Agreements (WRKSFWAGR)
CL command.





APAR Error Description / Circumvention

-----------------------------------------------
Corrupt *SRVPGM objects for SQL global variables can cause
looping (high CPU) in QSQRCHK.  One job that may loop as a
result of this problem is the QDBSRVXR2 database server job, but
the looping can occur in any job.  The stack traceback of the
looping job is similar to one of the following:

Program    Procedure
-------    ---------
QSQRCHK    QSQRCHK
QSQRCHK    TREEWALK
QSQRCHK    VISITWHERE
QSQRCHK    GETNODTYP
QSQRCHK    NODETYP

or

Program    Procedure
-------    ---------
QSQRCHK    QSQRCHK
QSQRCHK    GETSTYPE
QSQRCHK    NODETYP

Another symptom of corrupt *SRVPGM objects for SQL global
variables is an SQL7009 (MSGSQL7009) message with reason code 5
or 57.  Both of these reason codes indicate "Service program &1
in &2 is not a global variable)".

Either of these symptoms can occur when using SQL to reference a
corrupt global variable.

CORRECTION FOR APAR SE69096 :
-----------------------------
Application of this PTF will prevent additional corruption of
SQL global variables.  This PTF will not fix any corrupt SQL
global variables on the system.

Any SQL global variables which are corrupted should be dropped
and recreated after this PTF is applied.

CIRCUMVENTION FOR APAR SE69096 :
--------------------------------
None.


Activation Instructions


None.




Special Instructions


********************************************************************
THE FOLLOWING ARE SUPERSEDED SPECIAL INSTRUCTIONS. IF THE SUPERSEDED
PTF HAS ALREADY BEEN APPLIED AND ITS SPECIAL INSTRUCTION FOLLOWED,
IT IS NOT NECESSARY TO FOLLOW THAT SPECIAL INSTRUCTION AGAIN.
********************************************************************

SPECIAL INSTRUCTIONS FOR SUPERSEDED PTF SI39887 :
=================================================

To properly enable this PTF for prestarted server jobs,
you will need to stop and restart the SQL server jobs,
the Directory Services server jobs, the DRDA TCPIP server
jobs, and the host server jobs.  Otherwise,
the prestarted server jobs may continue to use older versions
of programs and unexpected results may occur.Applying this
PTF as delayed would achieve the same results.

To stop and restart the SQL server jobs, run the following:

ENDPJ SBS(QSYSWRK) PGM(QSQSRVR) OPTION(*IMMED)
STRPJ SBS(QSYSWRK) PGM(QSQSRVR)

To stop and restart the QRWTSRVR prestart jobs, run the following:

ENDPJ SBS(QUSRWRK) PGM(QRWTSRVR) OPTION(*IMMED)
STRPJ SBS(QUSRWRK) PGM(QRWTSRVR)

To stop and restart the DRDA TCPIP server jobs, run the following:

ENDTCPSVR SERVER(*DDM) OPTION(*IMMED)
STRTCPSVR SERVER(*DDM)

To stop and restart the Directory Services server job,
run the following:

ENDTCPSVR SERVER(*DIRSRV) OPTION(*IMMED)
STRTCPSVR SERVER(*DIRSRV)

To stop and restart the host server jobs, the following directions
must be followed.

The database server prestart jobs (QZDAINIT, QZDASOINIT, and
QZDASSINIT) by default are shipped to run in subsystem QSERVER
(QZDAINIT) and QUSRWRK (QZDASOINIT and QZDASSINIT).  However,
systems
can be customized to allow the database server prestart jobs to run
in other subsystems.

SYSTEMS WITH DEFAULT CONFIGURATION (database server prestart jobs
run
ONLY in the QSERVER and QUSRWRK subsystems).

---------------------------------------------------------------------

1) To ensure that the database server prestart jobs are ended,
issue the requests:

QSYS/ENDPJ SBS(QSERVER) PGM(QSYS/QZDAINIT) OPTION(*IMMED)
and
QSYS/ENDPJ SBS(QUSRWRK) PGM(QSYS/QZDASOINIT) OPTION(*IMMED)
and
QSYS/ENDPJ SBS(QUSRWRK) PGM(QSYS/QZDASSINIT) OPTION(*IMMED)
and if a non default prestart job configuration exists
QSYS/STRPJ SBS("subsystem-name") PGM(QSYS/QZDAINIT)

If the above commands fail with CPF0922, nothing more needs
to be done (the prestarted jobs are already inactive).

2) To re-enable the function after the PTF is applied, issue the
requests:

QSYS/STRPJ SBS(QSERVER) PGM(QSYS/QZDAINIT)
and
QSYS/STRPJ SBS(QUSRWRK) PGM(QSYS/QZDASOINIT)
and
QSYS/STRPJ SBS(QUSRWRK) PGM(QSYS/QZDASSINIT)
and if a non default prestart job configuration exists
QSYS/STRPJ SBS("subsystem-name") PGM(QSYS/QZDASSINIT)

If the above commands fail with CPF0921, nothing more needs
to be done (the prestarted jobs are already active).


Default Instructions

THIS PTF CAN BE APPLIED IMMEDIATE OR DELAYED.



Supersedes

PTF/FIX NO(S).  APAR TITLE LINE
--------------  ------------------------------------------------------------
   SI64534      OSP-DB Looping in QSQRCHK procedure NODETYP when processing
   SI64258      OSP-DB EXCESSIVE NATIVE OPENS OF QADBXRMTNM/QADBXRDBD FILES
   SI63157      OSP-DB-OTHER-LOOP QSQRUND MODULE QSQSWDS PROCEDURE SQSwitchD
   SI62984      OSP-DB-OTHER-F/QBNCRTBP-T/QBNCRTPG-MSGCPF5D02 IS RECEIVED WH
   SI62811      OSP-DB INTERNAL MAINTENANCE
   SI61307      OSP-DB INTERNAL MAINTENANCE
   SI59136      OSP-DB-MSGCPF8364-F/QDBGENSP-T/QDBGENSP SQL VARIABLE IO FAIL
   SI58795      OSP-DB Extended indicator setting not saved for SQL procedur
   SI58187      OSP-DB Allow extended indicators in dynamic sq in sql routin
   SI58165      OSP-DB INTERNAL MAINTENANCE
   SI58008      OSP-DB-MSGSQL0206 when column not defined at precompile time
   SI57398      OSP-DB-OTHER-THREADS-UNPRED RDB() SHOWS BLANK FOR AN RPG PGM
   SI57133      OSP-DB Internal Maintenance
   SI57088      OSP-DB-OTHER-UNPRED STORAGE LEAK USING STRDBMON HOSTVAR(*CON
   SI56769      OSP-DB REMOVE FIELD PROC FROM TRANSITION TABLES
   SI56414      OSP-DB-MSGCPF5035-T/QSQTRGIO INSTEAD OF TRIGGER DELETE TRANS
   SI54763      OSP-DB CREATE TRIGGER FAILS W/ TRANSITION TABLES OVER TABLE
   SI54028      OSP-DB Support HOSTVAR parameter on STRDBMON command - Phase
   SI53520      OSP-DB-OTHER-F/QSQDDL1-T/QSQDDL1-MSGMCH1210 SQL CREATE TABLE
   SI53456      OSP-DB Delimited names for create view in CCSID 1026
   SI53239      OSP-DB-OTHER-LOOP QSQRUND PROCEDURE SQSWITCHDIAGSPACE
   SI53197      OSP-DB MASKING LEFT ON AFTER SQL DISTINCT TYPE CREATED
   SI52971      OSP-DB-MSGCPF8364-F/QDBGENSP-T/QDBGENSP SQL VARIABLE IO FAIL
   SI52849      OSP-DB F/QSQSEQCDA-MSGSQL0601 Sequence operations with quali
   SI52737      OSP-DB-MSGSQL0604 CREATE TABLE with PARTITION BY RANGE using
   SI52311      OSP-DB UNABLE TO USE GENERATE SQL WITH PARTITIONED TABLE IF
   SI52259      OSP-DB Create view internal change for complex queries
   SI52010      OSP-DB INTERNAL MAINTENANCE
   SI51418      OSP-MSGSQL0204 ALTER TABLE FAILS WHEN ADDING A CONSTRAINT. D
   SI51397      OSP-DB Internal Maintenance
   SI50928      OSP-DB-SQL Dynamic Compound Statement Support
   SI50761      OSP-DB-OTHER-F/QDMROUTE-T/QSQTRGIO-MSGCPF2207
   SI47693      OSP-DB TRIGGER MEMORY LEAK
   SI39887      OSP-DB DB2ROW trigger inserts unexpected rows with MERGE sta
   SI39167      OSP-DB Internal Maintenance
   SI37207      OSP-DB MCH3601 F/QSQRUN2 PROCEDURE SQL_VALUESINTO X/10387 T/
   SI50563      OSP-DB-OTHER-F/QSQRUN4-T/QSQRUN4-MSGSQL0446 AFTER UPGRADING
   SI50408      OSP-DB-PERFM Create trigger with system generated names
   SI50225      OSP-DB SQL SET SESSION AUTHORIZATION CLOSES DDM & DRDA SOCKE
   SI50179      OSP-DB-OTHER-T/QSQRUN3-MSGSQL0313 DROP FAILS WITH MSGSQL0901
   SI49604      OSP-DB-OTHER-MSGSQL0636 ADDING A PARTITION GENERATES AND SQL
   SI49379      OSP-DB-OTHER-MSGSQL0901 with MSGCPF1021 f/QWCCCRVC ON CREATE
   SI49379      OSP-DB-OTHER MSGSQL9020 SQL SEQUENCE objects unusable when Q
   SI49372      OSP-DB ADD DB2 FOR i SUPPLIED OBJECTS
   SI49362      OSP-DB-OTHER-F/QSQDROP-T/QSQDROP-MSGSQL0204 MSGSQL0204 IF WE
   SI49338      OSP-DB ADD DB2 FOR i SUPPLIED OBJECTS
   SI49265      OSP-DB-OTHER-PERFM BAD PERFORMANCE DUE TO SQL LONG NAME RESO
   SI48932      OSP-DB-INCORROUT-MSGMCH3402 QSQSTATS
   SI48846      OSP-DB INTERNAL MAINTENANCE
   SI40766      OSP-DB-MSGSQL0478 f/QSQSEQCDA Unexpected failure on CREATE O
   SI40526      OSP-DB EXCLUSIVE LOCK REMAINS AFTER CREATE OR REPLACE SQL ST
   SI37751      OSP-DB-OTHER CREATE OR REPLACE PROCESSING
   SI37645      OSP-DB Internal Maintenance
   SI37442      OSP-DB INTERNAL MAINTENANCE
   SI45913      SQL-RUN-INCORROUT GENERATE SQL ON A PROCEDURES THAT CONTAINS
   SI36559      OSP-DB XML processing of SQLXSR objects
   SI44422      OSP-DB-INCORROUT SQL DETAILS FOR JOB DOES NOT REFRESH
   SI44209      OSP-DB SLOW RENAME LIBRARY PERFORMANCE
   SI43111      OSP-DB Connect By support
   SI41720      OSP-DB-MSGSQL0478 f/QSQVARDDL Unexpected failure on CREATE O
   SI41262      OSP-DB-OTHER Array Support Performance
   SI40523      OSP-DB EXCLUSIVE LOCK REMAINS AFTER CREATE OR REPLACE SQL ST
   SI38695      OSP-DB Internal Maintenance
   SI38568      OSP-DB Internal Maintenance
   SI38312      OSP-DB update sq objects with last change date
   SI37855      OSP-DB-MSGSQL0601 CREATE REPLACE VARIABLE WITH LONG NAME
   SI37762      OSP-DB Internal Maintenance
   SI37750      OSP-DB-OTHER CREATE OR REPLACE PROCESSING
   SI37741      OSP-DB-MSGSQL0454 CREATE DISTINCT TYPE
   SI37643      OSP-DB Internal Maintenance
   SI37441      OSP-DB INTERNAL MAINTENANCE
   SI37215      OSP-DB Internal Maintenance
   SI37157      OSP-DB INTERNAL MAINTENANCE
   SI36608      OSP-DB-QSQROUTE-MSGMCH3601 running in server mode
   SI39043      OSP-DB-OTHER-F/RMSLUNLOCKMIOBJECT -T/QSQCRTUT-MSGMCH2603 DUP
   SI48025      OSP-DB-OTHER-F/QSQDDL1-T/QSQDDL1-MSGMCH1210 SQL7032 CREATE T
   SI48024      OSP-DB INTERNAL MAINTENANCE
   SI47939      OSP-PAR JOB APPEARS TO GO INTO A LOOP, USING CPU, WHEN USING
   SI47437      OSP-DB In *SYS naming, allow '.' for qualifying objects
   SI47035      OSP-DB-OTHER-UNPRED DSPFD OF A SQL VIEW MAY SHOW THE INCORRE
   SI46175      OSP-DB IMPROVE PARALLEL BUILDING OF SQL TRIGGERS
   SI45566      OSP-DB-OTHER-F/QSQADITM-T/QSQSETTXT-MSGSQL0901 SQL0901 MESSA
   SI45175      OSP-DB MCH3601 F/QDMCLOSE X/0091 T/QDMCLOSE X/0091 WHEN CLOS
   SI44789      OSP-DB-OTHER-F/QRWTBAS-T/QRWTBAS-MSGMCH3601 SOME STORED PROC
   SI44012      OSP-DB-MSGSQL0901-T/QSQPROC on CREATE PROCEDURE
   SI44012      OSP-DB-MSGMCH3601-T/QSQROUTE PROCEDURE SAVOCL
   SI43915      OSP-DB-OTHER-F/QSQRUND-T/QSQSBAS-RC8488-MSGSQL0901
   SI43373      OSP-DB OBJECT NAME NOT CORRECT WHEN USING QGENOBJNAM
   SI42929      OSP-DB SPECIFY PROGRAM NAME ON CREATE TRIGGER
   SI42669      OSP-DB-MSGSQL0204 DROP of SQLXSR object gets not found when
   SI42122      OSP-DB Internal Maintenance
   SI42090      OSP-DB-F/QSQDDL1-T/QSQDDL1-MSGMCH1210 During forward convers
   SI42075      OSP-DB-MSGSQL7953 f/QSQVARDDL Unexpected success of DROP VAR
   SI41590      OSP-DB-UNPRED Use of three part name signalling SQL0204
   SI41226      OSP-DB-OTHER-T/QSQRUN3-MSGMCH3601 MODULE QSQOPEN
   SI40757      OSP-DB-UNPRED Three part alias statement rewrite
   SI40166      OSP-DB-OTHER-T/QSQDDL1-MSGMCH2601 MODULE QSQRTSEQ
   SI39899      OSP-DB-QSQASLOC-MSGSQL0204 when using specific name on ASSOC
   SI39877      OSP-DB-UNPRED Use of DDM for SQL three-part aliases failing
   SI39598      OSP-DB-INCORROUT SQL routines created with the Toolbox JDBC
   SI39596      OSP-DB Internal Maintenance
   SI39375      OSP-DB Internal Maintenance
   SI39338      OSP-DB-INCORROUT-MSGMCH3601 F/QSQVARIO/FINDPGMRELINV/14170 D
   SI38906      OSP-DB Internal Maintenance
   SI38459      OSP-DB-OTHER-INCORROUT INCORRECT CORRELATED TABLE NAME APPEA
   SI38440      OSP-DB Internal Maintenance
   SI38344      OSP-DB Internal Maintenance
   SI37889      OSP-DB Internal Maintenance
   SI37539      OSP-DB Internal Maintenance
   SI37312      OSP-DB-INCORROUT - MSGCPDB8EC F/QYASPPG1 T/QYASPSETG DOING S
   SI37047      OSP-DB-INCORROUT - MSGSQL0901 ON CREATE ENCODED VECTOR INDEX
   SI36734      OSP-DB XML PROCESSING
   SI36554      OSP DB GLOBAL VARIABLE PROCESSING

Summary Information

System..............................i
Models..............................
Release.............................V7R1M0
Licensed Program...............5770SS1
APAR Fixed..........................View details for APAR SE69096
Superseded by:......................
Recompile...........................Y
Library.............................QSYS
MRI Feature ........................NONE
Cum Level...........................NONE


System i Support

IBM disclaims all warranties, whether express or implied, including, but not limited to, the implied warranties of merchantability and fitness for a particular purpose. By furnishing this document, IBM grants no licenses to any related patents or copyrights. Copyright © 1996,1997,1998, 1999, 2000, 2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018 IBM Corporation. Any trademarks and product or brand names referenced in this document are the property of their respective owners. Consult the Terms of use link for trademark information.

[{"Type":"MASTER","Line of Business":{"code":"LOB57","label":"Power"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Platform":[{"code":"PF012","label":"IBM i"}],"Version":"7.1.0"},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG15V","label":"PTF Cover Letters - OS\/400 General"},"Component":"","ARM Category":[],"Platform":[{"code":"PF012","label":"IBM i"}],"Version":"V7R1M0","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
14 May 2018