IBM Support

SI33949 - OSP-DB ADD QSQSRVR subsystem control

PTF Cover Letter


PTF ( Program Temporary Fixes ) Cover letter


Order this fix

Abstract

OSP-DB ADD QSQSRVR subsystem control


Pre/Co-Requisite PTF / Fix List

REQ  LICENSED      PTF/FIX  LEVEL

TYPE PROGRAM  REL  NUMBER   MIN/MAX  OPTION
---- -------- ---  -------  -------  ------
CO   5761SS1  610  SI33076   00/00    0000
CO   5761SS1  610  SI33083   00/00    0000
CO   5761SS1  610  SI33084   00/00    0000
CO   5761SS1  610  SI33077   00/00    0000
CO   5761SS1  610  SI32976   00/00    0000
CO   5761SS1  610  SI31997   00/00    0000
DIST 5761SS1  610  SI33597   00/00    0000
DIST 5761SS1  610  SI31925   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

-----------------------------------------------
In some environments, it would be better to have SQL server mode
work occur in the same subsystem as the job which is initiating
the work.  Currently, QSQSRVR prestart jobs use the QSYSWRK
subsystem for all SQL server mode connections.  This PTF
provides a switch, in the form of an environment variable, which
allows the database to be directed to use the same subsystem as
the job which is forming the SQL server mode connection, instead
of relying upon QSYSWRK.

CORRECTION FOR APAR SE36073 :
-----------------------------
To enable the switch:

ADDENVVAR ENVVAR(QIBM_SRVRMODE_SBS) VALUE('*SAME') LEVEL(*SYS)
or
ADDENVVAR ENVVAR(QIBM_SRVRMODE_SBS) VALUE('') LEVEL(*SYS)

To disable the switch:

RMVENVVAR ENVVAR(QIBM_SRVRMODE_SBS) LEVEL(*SYS)

Note:
1) The subsystem choice is made upon first use of SQL server
mode within a job and does not change for the life of
that job.
2) The ADDENVVAR command could be used with LEVEL(*JOB), if
the same subsystem choice can't be made at a system wide
level.
3) If anything other than '*SAME' or '' appear within the
environment variable, QSYSWRK will continue to be used
for SQL server mode connections.
4) QSQSRVR jobs in QSYSWRK rely upon a prestart job entry for
optimal performance.  For any performance critical SQL server
mode activity, a prestart job entry for QSQSRVR should be
created for the subsystem used by the application.

For example:
ENDSBS <subsystem-name>
ADDPJE SBSD(<library>/<subsystem-description-name>)
PGM(QSYS/QSQSRVR) STRJOBS(*YES) INLJOBS(x) THRESHOLD(y)
ADLJOBS(z) MAXUSE(*NOMAX)
STRSBS <subsystem-name>

If a prestart job entry does not exist for QSQSRVR within the
subsystem, the QSQSRVR job will utilize a Batch Immediate
job (BCI) instead of a Prestart job (PJ).
5) If the current job's subsystem name is equal to 'QHTTPSVR' or
'ZEND', the environment variable will have no affect
and QSYSWRK will continue to be used.

CIRCUMVENTION FOR APAR SE36073 :
--------------------------------
none


Activation Instructions


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).





Special Instructions


None.


Default Instructions

THIS PTF CAN BE APPLIED IMMEDIATE OR DELAYED.



Supersedes

PTF/FIX NO(S).  APAR TITLE LINE
--------------  ------------------------------------------------------------
   SI33940      OSP-DB-MSGSQ30000-F/QSQROUTX
   SI33901      OSP-DB-OTHER-MSGSQL7022 WHEN ATTEMPTING TO CONNECT FROM ONE
   SI33901      OSP-DB-OTHER-INCORROUT CPYTOIMPF - PAD HEX DATA WITH X'40' R
   SI33663      OSP-DB-MSGSQL0330 F/QSQCLNUP FOR INSERT ON IGC FIELD
   SI33663      OSP-DB SELECT not stored in package from JDBC
   SI33632      OSP-DB Internal Maintenance
   SI33596      OSP-DB Add client register support for STRSQL and RUNSQLSTM
   SI33559      OSP-DB-OTHER-T/QQQSRVE1-MSGMCH3601 ON CALL TO STORED PROCEDU
   SI33365      OSP-DB-OTHER-MSGMCH5003-T/QQQSVDBG MODULE  QQDBHVRC
   SI33269      OSP-DB-OTHER-PERFM DIRECT MAPPING NOT USED FOR SELECT INTO S
   SI33222      OSP-XTND-OTHER PARAMETER MARKER NAMES NOT BEING RETURNED
   SI33078      OSP-DB Partioned table members not locked
   SI33080      OSP-INCORROUT INCORRECT NUMBER OF ROWS RETURNED, WHEN UDF IS
   SI33080      OSP-DB-OTHER-F/QSQDSCPT-T/QSQDSCPT-MSGMCH1210 AFTER UPGRADE
   SI32974      OSP-DB-PERFM IMPROVE UNFENCED UDF PERFORMANCE
   SI32915      OSP-DB-UNPRED Administration changes
   SI32828      OSP-DB-OTHER-F/QSQROUTS-T/QSQROUTS-MSGSQL0900 BATCH JOB RUNN
   SI32791      OSP-DB-MSGSQL0901 F/QSQRUN2 QSQFETCH ERROR TYPE 1203
   SI32728      OSP-DB-OTHER-F/QSQRUN3-T/QSQRUN3 INSERT FAILED WITH SQL0332,
   SI32648      OSP-DB-OTHER-F/QSQRUN2-T/QSQRUN2-MSGMCH3601 ON DRDA AR SELEC
   SI32579      SQL-RUN-F/QSQROUTE-T/QSQROUTE-MSGSQL0843 SQL RELEASE IS RECE
   SI32497      OSP-DB-MSGMCH3601 t/QSQFETCH with UDF running CQE
   SI32421      OSP-DB Incorrect SELECT from INSERT result with WHERE clause
   SI32333      OSP-DB-UNPRED XDA using unix sockets to qxdarecvr job failin
   SI32293      OSP-DB- MCH3601 in QSQROUTS
   SI32214      OSP-F/QSQRUN2-MSGMCH3601 PROCEDURE HANDLE_LOCATOR_THRESHOLD
   SI32214      OSP-DB-PERFM Single row fetches from embedded SQL program ne
   SI32214      OSP-DB-MSGSQL0913  AFTER CLOSE USING AUTOCOMMIT
   SI32196      OSP-DB-UNPRED XDA using unix sockets to qxdarecvr job failin
   SI32188      OSP-DB-OTHER-F/QSQVALUE-MSGMCH0601 AFTER UPGRADING FROM V5R3
   SI32131      OSP-DB Cursor position incorrect on fetch after last row
   SI32082      OSP-DB-MSGSQL0501 USING ODBC
   SI32082      OSP-OTHER HUNG JOB MUTEX WAIT
   SI31996      OSP-DB DB2_COLUMN_ROW_CHANGE not returned on GET DESCRIPTOR
   SI31926      OSP-DB CALL SQL statements omitted from packages
   SI31926      OSP-DB-INCORROUT DATABASE MONITOR RUNTIME
   SI31926      OSP-DB Cursor position incorrect on fetch after last row
   SI31825      OSP-DB ADD SERVER MODE CONNECTED THREAD to QUSRJOBI
   SI31825      OSP-DB-OTHER-T/QRWSQRY-MSGMCH0601 FETCHING LARGE BLOCK FROM
   SI31782      OSP-DB Cursor position incorrect on fetch after last row
   SI31730      OSP-DB-MSGSQL0901 SELECT INTO with INSERT in FROM and OVRDBF
   SI31642      OSP-DB-MSGSQL0204-F/QSQLABEL
   SI31480      OSP-DB MSGCPD4318 when attempting to insert literal into DBC
   SI31427      OSP-DB INTERNAL MAINTENANCE
   SI31437      OSP-DB-MSGSQL0900-F/QSQXCUTE ON QSQGNDDL API
   SI31432      OSP-DB INTERNAL MAINTENANCE
   SI31411      OSP-DB INTERNAL MAINTENANCE
   SI31340      OSP-DB-OTHER-INCORROUT EMBEDDED SQL RETURNS DEFAULT VALUES F
   SI31312      OSP-DB-MSGMCH3601-F/QSQROUTX-T/QSQROUTX AFTER MSGSQL0952
   SI31173      OSP-DB-F/QSQROUTE-MSGMCH1210 when attribute string is blank
   SI31150      OSP-DB-T/QZDASRV-MSGMCH3601 IN QZDASOINIT JOB WITH ISERIES A
   SI30975      OSP-DB-UNPRED Dynamic data format corrections over DRDA
   SI30871      OSP-DB-PERFM OPM PROGRAM CLEANUP TAKING LONGER AT R540
   SI30871      OSP-DB-INCORROUT Wrong SQLCODE or message information when m
   SI30818      OSP-DB-UNPRED Stored procedure with result sets causing DRDA
   SI30714      OSP-DB-Client registers not inherited for server mode
   SI30654      OSP-MSGMCH3601-PAR-940XMISC MCH3601 IN QP0LLFS1 RECEIVED FOR
   SI30654      OSP-DB-MSGMCH3402-F/QRWSERR FOLLOWING CONNECT
   SI30654      OSP-DB-PERFM OPM PROGRAM CLEANUP TAKING LONGER AT R540
   SI30639      OSP-DB-OTHER-SAP400 INCORRECT CONVERSION FROM FLOAT
   SI30622      OSP-DB-UNPRED Query EXTDTA space corrections
   SI30622      OSP-DB-OTHER-F/QSQROUTS-MSGSQL0313 JDBC APPLICATION GETTING
   SI30598      OSP-DB-OTHER-INCORROUT DUPLICATE DBMON 1000 RECORDS
   SI30552      OSP-DB-Client registers not inherited for server mode
   SI30468      OSP-DB-UNPRED ADD SET OPTION SQLCURRULE
   SI30372      OSP-DB-MSGSQL0904 on long running application
   SI30372      OSP-DB IMPROVE SERVICEABILITY FOR DESCRIPTOR AREA PROBLEMS
   SI30361      OSP-DB-UNPRED LOBs failing over DRDA
   SI30296      OSP-DB-MSGSQL0904 on long running application
   SI30271      OSP-DB-MSGMCH3601-T/QSQSTATS STMT 7984
   SI30254      OSP-DB SQL Server Mode QAQQINI INHERITANCE
   SI30240      OSP-DB-F/QSQFETCH-MSGSQL0501 running a dynaminc fetch or clo
   SI30185      OSP-DB INCORROUT Fetch does not return warnings
   SI30041      OSP-DB-F/QSQFETCH-MSGSQL0335 during character conversion
   SI29990      OSP-DB-MSGSQL0221-F/QSQROUTS FETCH FOR N ROWS
   SI29990      OSP-DB-MSGCPI433A-F/QQQSRVI1 Module/QQQAINI
   SI29966      OSP-DB WRONG VALUES IN QQINT05 COLUMN OF STRDBMON FILE
   SI29966      OSP-DB-F/QSQSTATS-MSGMCH0601
   SI29944      OSP-DB-F/QSQINS-MSGSQL0180 insert with mixed literal and par
   SI29944      OSP-DB-F/QSQINS-MSGSQL0180 prepared insert with literals and
   SI29890      OSP-DB-MSGMCH1210-T/QSQDSCPT- WHEN RUNNING PROGRAM WITH STRQ
   SI29890      OSP-DB-MSGSQL7055-F/QSQROUTS GENERATE SQL SCRIPTS FAILS
   SI29890      OSP-DB-OTHER-INCORROUT WRONG NUMBER OF RECORDS RETURNED
   SI29890      OSP-DB-OTHER INCORRECT REASON CODE FOR DATA CONVERSION IN ST
   SI29890      OSP-OTHER-SAP400-T/QSQRUN2-MSGMCH3601 AFTER A MSGCPF5027
   SI29824      OSP-DB-MSGSQL0802 DB2_REASON_CODE not set when running DRDA
   SI29824      OSP-DB-MSGSQL0904 on long running application
   SI29760      OSP-DB-PERFM IMPROVE FENCED UDF PERFORMANCE
   SI29732      OSP-DB Job temp storage growth from SQL descriptors
   SI29732      OSP-DB MSGSQL7034 using SQLStatistics API via native JDBC
   SI29634      OSP-DB-OTHER-SAP400 INCORRECT CONVERSION TO FLOAT ON FETCH
   SI29634      OSP-DB-MSGSQL0900 RUNSQLSTM
   SI29582      OSP-DB-MSGSQL7021-F/QSQROUTS-T/QSQROUTS DROP TYPE
   SI29582      OSP-DB-MSGCPF5173-T/QSQRUN2
   SI29481      OSP-DB PERFORMANCE Expression evaluator not used
   SI29481      OSP-DB-MSGSQL0420 DRDA SQL FETCH OR VALUES INTO
   SI29433      OSP-DB-INCORROUT MULTIPLE SQL0180 WARNINGS
   SI29402      OSP-DB-OTHER-SRCB6000103 SYSTEM CRASHES WITH SRCB6000103 WHE
   SI29402      OSP-DB-F/QSQFETCH-MSGMCH1210 assigning data to UTF-8 varialb
   SI29402      OSP-DB-INCORROUT SQL0335 not returned for char substitution
   SI29329      OSP-DB MCH3601 t/QSQFETCH using LOBs and DRDA
   SI29252      OSP-DB-F/QSQROUTE-MSGSQL0423 LOB locator not valid running D
   SI29252      OSP-DB Blocked insert for SELECT from INSERT
   SI29252      OSP-DB-MSGSQL7021-F/QSQROUTS-T/QSQROUTS CREATE SUMMARY TABLE
   SI29219      OSP-DB-UNPRED SUSPECT CODE GENERATION DURING PTF BUILD
   SI29118      OSp-DB-MSGMCH5003 T/QQQSVDBG
   SI29032      OSP-DB-UNPRED Decimal floating point warnings set
   SI28977      OSP-DB-F/QSQCONN-MSGMCH3601 when running XA
   SI28977      OSP-DB-MSGSQL0420 DRDA SQL FETCH OR VALUES INTO
   SI28977      OSP-DB-MSGSQL0180-F/QSQRUN3-T/QSQRUN3 BLOCKED INSERTS FAILIN
   SI28937      OSP-DB-MSGSQL0843 Timeout connecting to server job does not
   SI28937      OSP-DB-QSQDSCPT MSGMCH1210 in long running program
   SI28937      OSP-DB-MSGCPF4328-F/QTNOPNCLS-T/QSQRUN3 SQL7008 NOT VALID FO
   SI28856      OSP-DB-MSGMCH3402 F/QSQVALUE proc F_CLEANUP
   SI28856      OSP-DB-INCORROUT INCORRECT STATEMENT EXECUTED
   SI28856      OSP-DB-MSGMCH3601-F/QSQRUN3-T/QSQRUN3 ALTER PROCEDURE
   SI28856      OSP-DB-MSGMCH3601-F/QSQSTATS AFTER EXECUTING A SET STATEMENT
   SI28814      OSP-DB LOB BLOCKING BIT SET INCORRECTLY

Summary Information

System..............................System i
Models..............................
Release.............................V6R1M0
Licensed Program...............5761SS1
APAR Fixed..........................SE36073
Superseded by:......................View fix details for PTF SI56767
Recompile...........................N
Library.............................QSYS
MRI Feature ........................NONE
Cum Level...........................C9111610


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, 2019 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.

[{"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":"V6R1M0","Edition":""},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG16A","label":"PTF Cover Letters - i5\/OS V6R1 environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF012","label":"IBM i"}],"Version":"V6R1M0","Edition":""}]

Document Information

Modified date:
23 December 2008