SI49337 - OSP-DB ADD DB2 FOR i SUPPLIED OBJECTS

TYPE PROGRAM  REL  NUMBER   MIN/MAX  OPTION
---- -------- ---  -------  -------  ------
PRE  5770SS1  710  SI49247   00/00    0000
PRE  5770SS1  710  SI48685   00/00    0000
PRE  5770SS1  710  SI48642   00/00    0000
PRE  5770SS1  710  SI45893   00/00    0000
PRE  5770SS1  710  SI45856   00/00    0000
PRE  5770SS1  710  SI45901   00/00    0000
PRE  5770SS1  710  SI45828   00/00    0000
PRE  5770SS1  710  SI46166   00/00    0000
PRE  5770SS1  710  SI43643   00/00    0000
PRE  5770SS1  710  SI43651   00/00    0000
CO   5770SS1  710  SI49335   00/00    0000
CO   5770SS1  710  SI49331   00/00    0000
CO   5770SS1  710  SI48989   00/00    0000
CO   5770SS1  710  SI48847   00/00    0000
CO   5770SS1  710  SI48747   00/00    0000
CO   5770SS1  710  SI48018   00/00    0000
CO   5770SS1  710  SI46347   00/00    0000
CO   5770SS1  710  SI44494   00/00    0000
CO   5770SS1  710  SI44493   00/00    0000
CO   5770SS1  710  SI43510   00/00    0000
CO   5770SS1  710  SI43509   00/00    0000
CO   5770SS1  710  SI43566   00/00    0000
CO   5770SS1  710  SI43476   00/00    0000
CO   5770SS1  710  SI42001   00/00    0000
DIST 5770ST1  710  SI43654   NONE     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

-----------------------------------------------
To enhance DB2 for i family compatibility, the following objects
are being added:

New Catalogs:
SYSIBMADM/ENV_SYS_INFO
QSYS2/GROUP_PROFILE_ENTRIES

New User Defined functions:
SYSIBMADM/ENV_SYS_INFO
QSYS2/GROUP_USERS
SYSPROC/BASE_TABLE

CORRECTION FOR APAR SE54740 :
-----------------------------
The new DB2 for i supplied objects will be described in the SQL
Reference and within the IBM i Technology Updates wiki:

www.ibm.com/developerworks/ibmi/techupdates/db2

CIRCUMVENTION FOR APAR SE54740 :
--------------------------------
None.


DESCRIPTION OF PROBLEM FIXED FOR APAR SE54741 :
-----------------------------------------------
SQL applications need an SQL interface to extract JOB run
information.

CORRECTION FOR APAR SE54741 :
-----------------------------
The QSYS2/GET_JOB_INFO() User Defined Table Function accepts an
input paramter which is the target job name.
The UDTF finds the job and returns a single row table to the
caller.  The columns in the table provide detail about the job.
The input job name can be specified using the qualified or fully
qualified form.

Authorization requirements:
The caller must have *JOBCTL user special authority or
QIBM_DB_SQLADM or QIBM_DB_SYSMON function usage capability.
If the caller lacks one of those authorities, the UDTF
invocation will fail with SQLCODE=-443 and SQLSTATE='42501'.

GET_JOB_INFO() table definition:

V_JOB_STATUS CHAR(10) - the status of the target job.
Possible values include *ACTIVE, *OUTQ and *UNKNOWN.
V_ACTIVE_JOB_STATUS CHAR(4) - The active status of the initial
thread of the job.
If the job is not active, NULL is returned.  If the job is
active, the possible values are numerous, and include:
CNDW Waiting on handle-based condition.
LCKW Waiting for a lock.
RUN Currently running in the pool activity level.

V_RUN_PRIORITY INTEGER - The priority at which the job is
currently running, relative to other jobs on the system. The run
priority ranges from 0 (highest priority) to 99 (lowest
priority).
V_SBS_NAME CHAR(10) - The qualified name of the subsystem in
which an active job is running. The format of the qualified name
is a 10-character simple object name followed by a 10-character
library name. This value is only for jobs whose status is
*ACTIVE. For jobs with status of *OUTQ or *JOBQ, the value for
this field is blank.
V_CPU_USED BIGINT - Processing unit time used - total for the
job. The amount of processing unit time (in milliseconds) that
the job used across all routing steps.
V_TEMP_STORAGE_USED_MB INTEGER - Temporary storage used in
megabytes. The amount of auxiliary storage (in megabytes) that
is currently allocated to this job. This is an unsigned
BINARY(4) value.
V_AUX_IO_REQUESTED BIGINT - The number of auxiliary I/O requests
performed by the job across all routing steps. This includes
both database and nondatabase paging.
V_PAGE_FAULTS BIGINT - The number of times an active program
referenced an address that is not in main storage during the
current routing step of the specified job.
V_CLIENT_WRKSTNNAME CHAR(255) - The value of the SQL special
register value or NULL.
V_CLIENT_APPLNAME CHAR(255)   - The value of the SQL special
register value or NULL.
V_CLIENT_ACCTNG CHAR(255)     - The value of the SQL special
register value or NULL.
V_CLIENT_PROGRAMID CHAR(255)  - The value of the SQL special
register value or NULL.
V_CLIENT_USERID CHAR(255)     - The value of the SQL special
register value or NULL.

Example usage:

SELECT * FROM
TABLE(QSYS2.GET_JOB_INFO('690326/SCOTTF/QPADEV0003')) AS A
or
SELECT * FROM TABLE(QSYS2.GET_JOB_INFO('QzdasoinitQuser
690325')) AS A

CIRCUMVENTION FOR APAR SE54741 :
--------------------------------
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 SI41819 :
=================================================

When applying this PTF in immediate mode will result in exit program
processing that will drop and recreate DB2 for i supplied objects in
QSYS2, SYSIBM and several other system libraries.  The exit program may
not complete quickly.  It is recommended that when this PTF is applied
in immediate mode, that application which rely upon DB2 for i supplied
objects are quiesced.


Default Instructions

THIS PTF CAN BE APPLIED IMMEDIATE OR DELAYED.



Supersedes

PTF/FIX NO(S).  APAR TITLE LINE
--------------  ------------------------------------------------------------
   SI49213      OSP-DB-INCORROUT-MSGCPF2227-T/QSQCCATV
   SI48990      OSP-DB ADD CONTROL FOR Database Perspectives
   SI48820      OSP-DB ADD CATALOG TOOLS TO SYSTOOLS
   SI48792      OSP-DB ADD QSYS2/SQL_CHECK_AUTHORITY UDF
   SI48681      OSP-DB-OTHER-UNPRED Internal Maintenance
   SI48373      OSP-DB INTERNAL MAINTENANCE
   SI48152      OSP-DB INTERNAL MAINTENANCE
   SI48021      OSP-DB INTERNAL MAINTENANCE
   SI47840      OSP-DB Change qsqsysibm to use CREATE OR REPLACE
   SI47765      OSP-DB INTERNAL MAINTENANCE
   SI47561      OSP-DB INTERNAL MAINTENANCE
   SI47105      OSP-DB INTERNAL MAINTENANCE
   SI46924      OSP-DB-OTHER-T/QSQRLSPC-MSGMSGMCH3402
   SI46764      OSP-DB Improve performance of SYSIBM/SQLColPrivileges and SQ
   SI46622      OSP-DB-MSGSQL0332-T/QSQDELET CALLING QSYS2.REMOVE_PERFORMANC
   SI46437      OSP-DB INTERNAL MAINTENANCE
   SI46324      OSP-DB ADD WRAP AND CREATE_WRAPPED TO SYSIBMADM
   SI46200      OSP-DB-MSGSQL0206 using SYSTOOOLS.ACT_ON_INDEX_ADVICE proced
   SI45394      OSP-DB ADD procedure QSYS2.Reset_Table_Index_Statistics()
   SI45167      OSP-OSP-F/QQQVFMT-T/QDBXXREFSP-MSGCPD439C MSGCPD439C IN SCPF
   SI45082      OSP-DB ADD QSYS2/REMOVE_PERFORMANCE_MONITOR() PROCEDURE
   SI44948      OSP-DB ADD SYSTOOLS.REMOVE_INDEXES
   SI44948      OSP-DB SYSTOOLS.ACT_ON_INDEX_ADVICE ERROR HANDLING
   SI44937      OSP-DB-OTHER-UNPRED VIEW QSYS2/USAGE_PRIVILEGES MAY BE MISSI
   SI44913      OSP-DB QSQIBMCHK should not check for QSQPDTBL
   SI44686      OSP-DB ADD QSYS2/SYSDISKSTAT catalog
   SI44496      OSP-DB Use MTIs as a source of statistics
   SI44366      OSP-DB-MSGSQL0104 FROM QSQGNDDL() API GENERATED SQL
   SI44167      OSP-DB-MSGMCH2601-T/QSQCCATV GetIdentityValue install failur
   SI44069      OSP-DB ADD QSYS2/Journal_Entry_Info() procedure
   SI43658      OSP-DB ADD QSYS2.SQLQMProfilesAudit for improved audit suppo
   SI43568      OSP-DB ADDITIONAL TCPIP FILTERING ENHANCEMENTS
   SI43475      OSP-DB-OTHER-UNPRED-UNPRED Support for JDBC 4.1
   SI43429      OSP-DB ADD CHECKS TO QSYS/QSQIBMCHK
   SI43271      OSP-DB-INCORROUT QSYS2/OBJECT_STATISTICS OBJCREATED column
   SI42721      OSP-DB ADD CONSISTENCY_TOKEN COLUMN to QSYS2/SYSPROGRAMSTAT
   SI42586      OSP-INCORROUT Base Function to Enable Future Extensions
   SI41967      OSP-DB Internam Maintenance
   SI41880      OSP-DB ADD QSYS2/TCPIP_INFO Procedure and View
   SI41819      OSP-DB ADD QSYS2/FIND_AND_CANCEL_QSQSRVR_SQL and DUMP_SQL_CU
   SI41819      OSP-DB ADD QSYS2/GET_HOST_DATA() procedure
   SI40843      OSP-DB Add QSYS2/SYSPARTITIONDISK AND SYSPARTITIONINDEXDISK
   SI40634      OSP-DB-OTHER-UNPRED MULTIPLE ROWS APPEAR IN QSYS2/SYSPACKAGE
   SI40418      OSP-DB Internal Maintenance
   SI40101      OSP-DB ADD QSYS2.FIND_QSQSRVR_JOBS() procedure
   SI40101      OSP-DB Internal Maintenance
   SI39931      OSP-DB ADD QSYS2/INDEX_ADVICE PROCEDURE
   SI39820      OSP-DB Improve QSYS2/SYSPARTITIONDISK FOR SSD
   SI39820      OSP-DB Add QSYS2/Display_Journal UDTF
   SI39336      OSP-DB Internal Maintenance
   SI39225      OSP-DB-OTHER-UNPRED DYNDFTCOL REMAINS SET AFTER CALL TO QSQS
   SI39054      OSP-DB INTERNAL CHANGES TO SUPPORT MOVE/RENAME TEXT SEARCH I
   SI38564      OSP-DB Internal Maintenance
   SI38064      OSP-DB Internal Maintenance
   SI37814      OSP-DB Internal Maintenance
   SI37634      OSP-DB-QSQCAT-MSGCPF7002 on vary on of independent ASP
   SI37526      OSP-DB Internal Maintenance
   SI37402      OSP-DB Internal Maintenance
   SI37227      OSP-DB Internal Maintenance
   SI37118      OSP-DB Internal Maintenance
   SI36961      OSP-DB INTERNAL MAINTENANCE
   SI36808      OSP-DB INTERNAL MAINTENANCE
   SI36611      OSP-DB-QSQROUTE-MSGMCH3601 running in server mode
   SI40917      OSP-DB FILES DELETED AFTER INSTALLING PTF'S
   SI37005      OSP-DB-RUN reset identity counters on SYSTXT catalogs

Summary Information

IBM i Support