ADMIN_DS_BROWSE stored procedure

The SYSPROC.ADMIN_DS_BROWSE stored procedure returns either text or binary records from certain data sets or their members. You can browse a physical sequential (PS) data set, a generation data set, a partitioned data set (PDS) member, or a partitioned data set extended (PDSE) member. This stored procedure supports only data sets with LRECL=80 and RECFM=FB.

Environment

Begin general-use programming interface information.

The load module for ADMIN_DS_BROWSE, DSNADMDB, must reside in an APF-authorized library. ADMIN_DS_BROWSE runs in a WLM-established stored procedures address space, and all libraries in this WLM procedure STEPLIB DD concatenation must be APF-authorized.

Authorization

To execute the CALL statement, the owner of the package or plan that contains the CALL statement must have one or more of the following privileges on each package that the stored procedure uses:

The EXECUTE privilege on the package for DSNADMDB
Ownership of the package
PACKADM authority for the package collection
SYSADM authority

The ADMIN_DS_BROWSE caller also needs authorization from an external security system, such as RACF®, in order to browse or view an z/OS® data set resource.

Syntax

The following syntax diagram shows the SQL CALL statement for invoking this stored procedure:

Option descriptions

data-type

Specifies the type of data to be browsed and how the data set will be allocated. Possible values are:

1: Text data with exclusive access
2: Binary data with exclusive access
3: Text data with shared access. This value is valid only if browsing a library member.
4: Binary data with shared access. This value is valid only if browsing a library member.

This is an input parameter of type INTEGER and cannot be null.

data-set-name

Specifies the name of the data set, or of the library that contains the member to be browsed. Possible values are:

PS data set name: If reading from a PS data set, the data-set-name contains the name of the PS data set.
PDS or PDSE name: If reading from a member that belongs to this PDS or PDSE, the data-set-name contains the name of the PDS or PDSE.
GDS name: If reading from a generation data set, the data-set-name contains the name of the generation data set, such as USERGDG.FILE.G0001V00.

This is an input parameter of type CHAR(44) and cannot be null.

member-name

Specifies the name of the PDS or PDSE member, if reading from a PDS or PDSE member. Otherwise, a blank character.

This is an input parameter of type CHAR(8) and cannot be null.

dump-option

Specifies whether to use the Db2 standard dump facility to dump the information necessary for problem diagnosis when an SQL error occurred or when a call to the IBM® routine IEFDB476 to get messages about an unsuccessful SVC 99 call failed.

Possible values are:

Y: Generate a dump.
N: Do not generate a dump.

This is an input parameter of type CHAR(1) and cannot be null.

return-code

Provides the return code from the stored procedure. Possible values are:

0: The call completed successfully.
12: The call did not complete successfully. The message output parameter contains messages describing the error.

This is an output parameter of type INTEGER.

message

Contains messages describing the error encountered by the stored procedure. If no error occurred, then no message is returned.

The first messages in this area are generated by the stored procedure. Messages that are generated by Db2 or by z/OS might follow the first messages.

This is an output parameter of type VARCHAR(1331).

Example

The following C language sample shows how to invoke ADMIN_DS_BROWSE:

#include    <stdio.h>
#include    <stdlib.h>
#include    <string>

/******************** DB2 SQL Communication Area ********************/
 EXEC SQL INCLUDE SQLCA;

 int main( int argc, char *argv[] )    /* Argument count and list    */
 {
   /****************** DB2 Host Variables ****************************/
   EXEC SQL BEGIN DECLARE SECTION;

   /* SYSPROC.ADMIN_DS_BROWSE parameters                             */
   long int    datatype;               /* Data type                  */
   char        dsname[45];             /* Data set name              */
   char        mbrname[9];             /* Library member name        */
   char        dumpopt[2];             /* Dump option                */
   long int    retcd;                  /* Return code                */
   char        errmsg[1332];           /* Error message              */

   /* Result set locators                                            */
   volatile SQL TYPE IS RESULT_SET_LOCATOR *rs_loc1;

   /* Result set row                                                 */
   long int    rownum;                 /* Sequence number of the     */
                                       /*   table row                */
   char        text_rec[81];           /* A data set record          */
   EXEC SQL END DECLARE SECTION;

   /******************************************************************/
   /* Assign values to input parameters to browse a library member   */
   /******************************************************************/
   datatype = 1;
   strcpy(dsname, "USER.DATASET.PDS");
   strcpy(mbrname, "MEMBER0A");
   strcpy(dumpopt, "N");

   /******************************************************************/
   /* Call stored procedure SYSPROC.ADMIN_DS_BROWSE                  */
   /******************************************************************/
   EXEC SQL CALL SYSPROC.ADMIN_DS_BROWSE
                        (:datatype, :dsname, :mbrname, :dumpopt,
                         :retcd,    :errmsg);

   /******************************************************************/
   /* Retrieve result set when the SQLCODE from the call is +446,    */
   /* which indicates that result sets were returned                 */
   /******************************************************************/
   if (SQLCODE == +466)               /* Result sets were returned   */
   {
     /* Establish a link between the result set and its locator      */
     EXEC SQL ASSOCIATE LOCATORS (:rs_loc1)
              WITH PROCEDURE SYSPROC.ADMIN_DS_BROWSE;

     /* Associate a cursor with the result set                       */
     EXEC SQL ALLOCATE C1 CURSOR FOR RESULT SET :rs_loc1;

     /* Perform fetches using C1 to retrieve all rows from the       */
     /* result set                                                   */
     EXEC SQL FETCH C1 INTO :rownum, :text_rec;
     while(SQLCODE==0)
     {
       EXEC SQL FETCH C1 INTO :rownum, :text_rec;
     }

     EXEC SQL CLOSE C1;
   }

   return(retcd);
 }

Output

This stored procedure returns the following output parameters, which are described in Option descriptions:

return-code
message

In addition to the preceding output, the stored procedure returns one result set that contains the text or binary records read.

The following table shows the format of the result set returned in the created global temporary table SYSIBM.TEXT_REC_OUTPUT containing text records read:

Table 1. Result set row for ADMIN_DS_BROWSE result set (text records)
Column name	Data type	Contents
ROWNUM	INTEGER	Sequence number of the table row, from 1 to `n`.
TEXT_REC	VARCHAR(80)	Record read (text format).

The following table shows the format of the result set returned in the created global temporary table SYSIBM.BIN_REC_OUTPUT containing binary records read:

Table 2. Result set row for ADMIN_DS_BROWSE result set (binary records)
Column name	Data type	Contents
ROWNUM	INTEGER	Sequence number of the table row, from 1 to `n`.
BINARY_REC	VARCHAR(80) FOR BIT DATA	Record read (binary format).

End general-use programming interface information.