www.ruban.de – DB2 experts web site: REXX procedure to call ADMIN_INFO_SYSPARM REXX to call the IBM DB2-supplied stored procedure ADMIN_INFO_SYSPARM to access DB2 DSNZPARM parameters and writes them an output dataset. This make it easy to archive DB2 subsystem parameters or compare parameter sets of different DB2 subsystems. See example output at end of page. DIS2INST – display ZPARM’s using ADMIN_INFO_SYSPARM » www.ruban.de

Archives from month » Februar, 2012

REXX procedure to call ADMIN_INFO_SYSPARM

REXX to call the IBM DB2-supplied stored procedure ADMIN_INFO_SYSPARM to access DB2 DSNZPARM parameters and writes them an output dataset. This make it easy to archive DB2 subsystem parameters or compare parameter sets of different DB2 subsystems.

See example output at end of page.

 /*REXX****************************************************************/
 /* DIS2INST ...: Display DB2 for z/OS installation parameters using  */
 /*               IBM-supplied stored procedure ADMIN_INFO_SYSPARM    */
 /*-------------------------------------------------------------------*/
 /* CALLED BY..: IKJEFT01   - TSO Batch oder Online                   */
 /* ARGUMENTS..: SSID       - Local/Home Subsystem                    */
 /* CALLS......: DSNREXX  M - DB2/REXX Interface                      */
 /*              XMPABND  M - Abend U0001 (Non-ISPF)                  */
 /*-------------------------------------------------------------------*/
 /* COMMENTS...: Verify that SDSNLOAD ist part of LINKLIST or already */
 /*              allocated to TSO session. If not, you need to        */
 /*              dynamically allocate. (See STEPLIBX in code below!)  */
 /*-------------------------------------------------------------------*/
 /* FUNCTION...: Calls ADMIN_INFO_SYSPARM stored procedure to obtain  */
 /*              DB2 system installation values.                      */
 /*-------------------------------------------------------------------*/
 /* HISTORY....: 06.04.11/G.Ruban - V1R0                              */
 /*********************************************************************/
 x = MSG("OFF")

 /*********************************************************************/
 /* process arguments passed to procedure                             */
 /*********************************************************************/
 parse upper arg arglist
 ver = "1.0"
 Numeric Digits 12

 if pos("DEBUG",arglist)>0
 then do
      debug = "DEBUG"
      TRACE ?I
      SQLTRACE = "TRACE"
 end

 /*********************************************************************/
 /* Some environment information and initialzations                   */
 /*********************************************************************/
 PARSE SOURCE,
    source_host_env,
    source_call_type,
    source_uc_exec_name,
    source_hostdd,
    source_hostdsn,
    source_nf_exec_name,
    source_initial_cmd_env,
    source_asid_name

 jobnamestep   = Get_Jobname()
 sysvar_sysuid = SYSVAR("SYSUID")
 sysvar_sysenv = SYSVAR("SYSENV")

 /*********************************************************************/
 /* Header                                                            */
 /*********************************************************************/
 "CLRSCRN"
 say copies("#",79)
 say "# DIS2INST V"ver" - DSNHDECP DB2 for z/OS installation values",
     date("O")" "time()" #";
 say copies("#",79)
 say ""

 /*********************************************************************/
 /* Cechk required environment                                        */
 /*********************************************************************/
 if (source_host_env ^= "TSO") then do
   Say "Wrong host environment" source_host_env
   signal error
 end

 parse var arglist ssid
 if debug = "DEBUG"
 then do
      i = pos("DEBUG",ssid)
      if i>0 then ssid = delstr(ssid,i,5)
      i = wordpos("DEBUG",CIRTERIA)
      if i>9 then CRITERIA = delword(CRITERIA,i,1)
 end

 if ssid = "" ! ssid = "DEBUG"
 then do
      say "Executing without arguments,",
          "setting default DB2 subsystem id and criteria."
      ssid     = "DB2T"
      CRITERIA = "DBNAME LIKE 'DSN%'",          /* WHERE CLAUSE 4096 */
                 "AND NAME LIKE '%'"
 end
 CRITERIA_IND = 0

 /*********************************************************************/
 /* If DB2 Load Library is not part of z/OS LINKLIST:                 */
 /* Allocate SDSNLOAD using CA TSOPLUS (or any other which adds the   */
 /* DB2 load libray dynamically to the STEPLIB).                      */
 /*********************************************************************/
  address TSO
  "FREE  FI(STEPLIBX) DA('SAMPLE."ssid".SDSNLOAD')"
  "ALLOC FI(STEPLIBX) DA('SAMPLE."ssid".SDSNLOAD') SHR REUSE"
  if rc <> 0
  then do
       say "DB2 SDSNLOAD library for SSID="ssid" is not available!"
       say "Check z/OS LINKLIST or allocate to STEPLIB in advance!"
       signal error
  end

  ADDRESS TSO "SUBCOM DSNREXX"
  if rc <> 0
  then do
       cc = RXSUBCOM("ADD","DSNREXX","DSNREXX")
       if cc
       then do
            say "DB2 SDSNLOAD library not available!"
            say "Check z/OS LINKLIST or allocate to STEPLIB in advance!"
            signal error
       end
  end

 /*********************************************************************/
 /* StoProc in/out fields and corresponding stuff                     */
 /*********************************************************************/
 DB2_MEMBER         = "";         DB2_MEMBER_IND            = -1;
 RETURN_CODE        = 0;          RETURN_CODE_IND           = -1;
 MSG                = "";         MSG_IND                   = -1;

 /*********************************************************************/
 /* call DB2 stored procedure                                         */
 /*********************************************************************/
 sqlstmt = "CALL SYSPROC.ADMIN_INFO_SYSPARM ",
              "(:DB2_MEMBER         :DB2_MEMBER_IND, " ,
              " :RETURN_CODE        :RETURN_CODE_IND, " ,
              " :MSG                :MSG_IND)";

 ADDRESS DSNREXX "CONNECT" ssid
 call Code 0

 address DSNREXX "EXECSQL" sqlstmt
 call Code 0 466                        /* +466 = more rows to fetch */

 if returncode = 0
 then do
      say "DB2 for z/OS DSNHDECP values ..."
      say copies("-",79)
 end
 /*********************************************************************/
 /* Associate locator with stored procedure                           */
 /*********************************************************************/
 LOC1 = d2x(0)
 sqlstmt = "ASSOCIATE LOCATORS (:LOC1)",
           "WITH PROCEDURE SYSPROC.ADMIN_INFO_SYSPARM"
 address DSNREXX "EXECSQL" sqlstmt;
 call code 0

 /*********************************************************************/
 /* Allocate and process cursor-driven                                */
 /*********************************************************************/
 sqlstmt = "ALLOCATE C101 CURSOR FOR RESULT SET :LOC1"
 address DSNREXX "EXECSQL" sqlstmt;
 call Code 0

 /*********************************************************************/
 /* Fetch all messages via the cursor                                 */
 /* ROWNUM   Sequence number of the table row, from 1 to n.           */
 /* MACRO    Macro that contains the system parameter, the DSNHDECP   */
 /*          parameter, or the name of the IRLM procedure that z/OS   */
 /*          invokes if IRLM is started automatically by DB2.         */
 /* PARAMETER  Name of the system parameter, DSNHDECP parameter, or   */
 /*            IRLM parameter.                                        */
 /* INSTALL_PANEL  Name of the installation panel where the parameter */
 /*                value can be changed when installing or            */
 /*                migrating DB2.                                     */
 /* INSTALL_FIELD  Name of the parameter on the installation panel.   */
 /* INSTALL_LOCATION Location of the parameter on the installation    */
 /*                  panel.                                           */
 /* VALUE    The value of the parameter.                              */
 /* ADDITIONAL_INFO Reserved for future use.                          */
 /*********************************************************************/
 sqlstmt = "FETCH C101 INTO ",
                 ":ROWNUM, :MACRO,  :PARAMETER, ",
                 ":INSTALL_PANEL    :IND_INSTALL_PANEL, ",
                 ":INSTALL_FIELD    :IND_INSTALL_FIELD, ",
                 ":INSTALL_LOCATION :IND_INSTALL_LOCATION, ",
                 ":VALUE, ",
                 ":ADDITIONAL_INFO  :IND_ADDITIONAL_INFO ";
 do while sqlcode = 0
    if IND_INSTALL_PANEL   <> -1
    then IPNL = INSTALL_PANEL
    else IPNL = "n/a"
    if IND_INSTALL_FIELD   <> -1
    then IFLD = INSTALL_FIELD
    else IFLD = "n/a"
    if IND_INSTALL_LOCATION<> -1
    then ILOC = INSTALL_LOCATION
    else ILOC = "n/a"
    if IND_ADDITIONAL_INFO <> -1
    then IADI = ADDITIONAL_INFO
    else IADI = "n/a"
    say left(macro,8)"!"left(parameter,24)"!" !!,
        left(IFLD,24)"!"left(value,20)
    if ROWNUM < 1
    then say copies("-",8)"!"copies("-",24)"!" !!,
             copies("-",24)"!"copies("-",20)
    Address DSNREXX "EXECSQL" SQLSTMT
 end
 call code 0 100

 sqlstmt = "CLOSE C101"
 address DSNREXX "EXECSQL "sqlstmt;
 call code 0 100

 say "*** end ***"

/*********************************************************************/
/* Disconnect vom Subsystem                                          */
/*********************************************************************/
Disconnect:
    ADDRESS DSNREXX "DISCONNECT"
    cc = RXSUBCOM("DELETE","DSNREXX","DSNREXX")
return
/*********************************************************************/
/* Error processing and termination                                  */
/*********************************************************************/
SYNTAX:
      say "DIS2INST: Syntax error."
      say "          [TSO] DIS2INST ssid options"
      say "          ssid .......: DB2 Subsystem ID"
      say "          criteria ...: criteria for DSNACCOX"
ERROR:
      say ""
      say "Current enviroment:" jobnamestep source_host_env,
          source_call_type source_uc_exec_name source_hostdd,
          source_hostdsn source_nf_exec_name source_initial_cmd_env
      If Sysvar(SYSISPF) = "NOT ACTIVE"
      then do
           say "REXX procedure DIS2INST ends with U0001."
           address ATTACH "XMPABND"
           exit 0
      end
      say "REXX procedure DIS2INST ends with RC=20."
exit 20

/*********************************************************************/
/* Check SQLCODE's allowed                                           */
/*********************************************************************/
Code:
   arg codes
   allowed = "NO"
   do ci=1 to words(codes)
      if sqlcode = word(codes,ci)
      then allowed = "YES"
   end
   if allowed = "NO"
   then do
        say "CONNECT: SQL Error with SQLCODE="sqlcode
        call TIAR sqlcode
        say "         "sqlstmt
        call Disconnect
        signal error
   end
return

/*********************************************************************/
/* process line output, hide if value contains no information        */
/*********************************************************************/
Line: arg lval ltext
      lval = trunc(lval)
      if lval > 0
      then say right(lval,20)" "ltext
return

/*********************************************************************/
/* Retrieve some internal information from z/OS control blocks       */
/*********************************************************************/
GET_JOBNAME: Procedure
   /* all fields are part of general use prog'ing interface */
   PSATOLD  = '21C'X
   TCB      = C2D(STORAGE(C2X(PSATOLD),4))
   TIOT     = C2D(STORAGE(D2X(TCB+12),4))
   TIOCNJOB = STRIP(STORAGE(D2X(TIOT),8))
   TIOCSTEP = STRIP(STORAGE(D2X(TIOT+8),8))
Return TIOCNJOB TIOCSTEP

/*********************************************************************/
/* Display SQL Commmunication area using DSNTIAR                     */
/*********************************************************************/
TIAR: arg sqlcode
      sqlc = d2x(sqlcode,8)
      sqlc = x2c(sqlc)

      sqlca = 'sqlca   '
      sqlca = sqlca !!  x2c(00000088)
      sqlca = sqlca !!  sqlc
      sqlca = sqlca !!  x2c(0000)
      sqlca = sqlca !!  copies(' ',78)
      sqlca = sqlca !!  copies(x2c(00),24)
      sqlca = sqlca !!  copies(' ',16)
      tiar_msg = x2c(0190)copies(' ',400)
      text_len = x2c(00000050) ;
      /***************************************************************/
      /* Extract Message from Return Area                            */
      /***************************************************************/
      ADDRESS ATTCHPGM 'DSNTIAR SQLCA TIAR_MSG TEXT_LEN'
      say substr(tiar_msg,4,400);
      /***************************************************************/
      /* Extract Message from Return Area                            */
      /***************************************************************/
      len = c2d(substr(rtrnarea,5,2))
      /***************************************************************/
      /* Extract Message from Return Area                            */
      /***************************************************************/
      say "          SQLCODE="sqlcode
      say "          SQLERRMC="sqlerrmc
      say "          SQLWARN5="sqlwarn.5
      say "          SQLERRP ="sqlerrp
      say "          SQLSTATE="sqlstate
return

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

/*REXX****************************************************************/

/* DIS2INST ...: Display DB2 for z/OS installation parameters using */

/* IBM-supplied stored procedure ADMIN_INFO_SYSPARM */

/*-------------------------------------------------------------------*/

/* CALLED BY..: IKJEFT01 - TSO Batch oder Online */

/* ARGUMENTS..: SSID - Local/Home Subsystem */

/* CALLS......: DSNREXX M - DB2/REXX Interface */

/* XMPABND M - Abend U0001 (Non-ISPF) */

/*-------------------------------------------------------------------*/

/* COMMENTS...: Verify that SDSNLOAD ist part of LINKLIST or already */

/* allocated to TSO session. If not, you need to */

/* dynamically allocate. (See STEPLIBX in code below!) */

/*-------------------------------------------------------------------*/

/* FUNCTION...: Calls ADMIN_INFO_SYSPARM stored procedure to obtain */

/* DB2 system installation values. */

/*-------------------------------------------------------------------*/

/* HISTORY....: 06.04.11/G.Ruban - V1R0 */

/*********************************************************************/

x = MSG("OFF")

/*********************************************************************/

/* process arguments passed to procedure */

/*********************************************************************/

parse upper arg arglist

ver = "1.0"

Numeric Digits 12

if pos("DEBUG",arglist)>0

then do

debug = "DEBUG"

TRACE ?I

SQLTRACE = "TRACE"

end

/*********************************************************************/

/* Some environment information and initialzations */

/*********************************************************************/

PARSE SOURCE,

source_host_env,

source_call_type,

source_uc_exec_name,

source_hostdd,

source_hostdsn,

source_nf_exec_name,

source_initial_cmd_env,

source_asid_name

jobnamestep = Get_Jobname()

sysvar_sysuid = SYSVAR("SYSUID")

sysvar_sysenv = SYSVAR("SYSENV")

/*********************************************************************/

/* Header */

/*********************************************************************/

"CLRSCRN"

say copies("#",79)

say "# DIS2INST V"ver" - DSNHDECP DB2 for z/OS installation values",

date("O")" "time()" #";

say copies("#",79)

say ""

/*********************************************************************/

/* Cechk required environment */

/*********************************************************************/

if (source_host_env ^= "TSO") then do

Say "Wrong host environment" source_host_env

signal error

end

parse var arglist ssid

if debug = "DEBUG"

then do

i = pos("DEBUG",ssid)

if i>0 then ssid = delstr(ssid,i,5)

i = wordpos("DEBUG",CIRTERIA)

if i>9 then CRITERIA = delword(CRITERIA,i,1)

end

if ssid = "" ! ssid = "DEBUG"

then do

say "Executing without arguments,",

"setting default DB2 subsystem id and criteria."

ssid = "DB2T"

CRITERIA = "DBNAME LIKE 'DSN%'", /* WHERE CLAUSE 4096 */

"AND NAME LIKE '%'"

end

CRITERIA_IND = 0

/*********************************************************************/

/* If DB2 Load Library is not part of z/OS LINKLIST: */

/* Allocate SDSNLOAD using CA TSOPLUS (or any other which adds the */

/* DB2 load libray dynamically to the STEPLIB). */

/*********************************************************************/

address TSO

"FREE FI(STEPLIBX) DA('SAMPLE."ssid".SDSNLOAD')"

"ALLOC FI(STEPLIBX) DA('SAMPLE."ssid".SDSNLOAD') SHR REUSE"

if rc <> 0

then do

say "DB2 SDSNLOAD library for SSID="ssid" is not available!"

say "Check z/OS LINKLIST or allocate to STEPLIB in advance!"

signal error

end

ADDRESS TSO "SUBCOM DSNREXX"

if rc <> 0

then do

cc = RXSUBCOM("ADD","DSNREXX","DSNREXX")

if cc

then do

say "DB2 SDSNLOAD library not available!"

say "Check z/OS LINKLIST or allocate to STEPLIB in advance!"

signal error

end

/*********************************************************************/

/* StoProc in/out fields and corresponding stuff */

/*********************************************************************/

DB2_MEMBER = ""; DB2_MEMBER_IND = -1;

RETURN_CODE = 0; RETURN_CODE_IND = -1;

MSG = ""; MSG_IND = -1;

/*********************************************************************/

/* call DB2 stored procedure */

/*********************************************************************/

sqlstmt = "CALL SYSPROC.ADMIN_INFO_SYSPARM ",

"(:DB2_MEMBER :DB2_MEMBER_IND, " ,

" :RETURN_CODE :RETURN_CODE_IND, " ,

" :MSG :MSG_IND)";

ADDRESS DSNREXX "CONNECT" ssid

call Code 0

address DSNREXX "EXECSQL" sqlstmt

call Code 0 466 /* +466 = more rows to fetch */

if returncode = 0

then do

say "DB2 for z/OS DSNHDECP values ..."

say copies("-",79)

end

/*********************************************************************/

/* Associate locator with stored procedure */

/*********************************************************************/

LOC1 = d2x(0)

sqlstmt = "ASSOCIATE LOCATORS (:LOC1)",

"WITH PROCEDURE SYSPROC.ADMIN_INFO_SYSPARM"

address DSNREXX "EXECSQL" sqlstmt;

call code 0

/*********************************************************************/

/* Allocate and process cursor-driven */

/*********************************************************************/

sqlstmt = "ALLOCATE C101 CURSOR FOR RESULT SET :LOC1"

address DSNREXX "EXECSQL" sqlstmt;

call Code 0

/*********************************************************************/

/* Fetch all messages via the cursor */

/* ROWNUM Sequence number of the table row, from 1 to n. */

/* MACRO Macro that contains the system parameter, the DSNHDECP */

/* parameter, or the name of the IRLM procedure that z/OS */

/* invokes if IRLM is started automatically by DB2. */

/* PARAMETER Name of the system parameter, DSNHDECP parameter, or */

/* IRLM parameter. */

/* INSTALL_PANEL Name of the installation panel where the parameter */

/* value can be changed when installing or */

/* migrating DB2. */

/* INSTALL_FIELD Name of the parameter on the installation panel. */

/* INSTALL_LOCATION Location of the parameter on the installation */

/* panel. */

/* VALUE The value of the parameter. */

/* ADDITIONAL_INFO Reserved for future use. */

/*********************************************************************/

sqlstmt = "FETCH C101 INTO ",

":ROWNUM, :MACRO, :PARAMETER, ",

":INSTALL_PANEL :IND_INSTALL_PANEL, ",

":INSTALL_FIELD :IND_INSTALL_FIELD, ",

":INSTALL_LOCATION :IND_INSTALL_LOCATION, ",

":VALUE, ",

":ADDITIONAL_INFO :IND_ADDITIONAL_INFO ";

do while sqlcode = 0

if IND_INSTALL_PANEL <> -1

then IPNL = INSTALL_PANEL

else IPNL = "n/a"

if IND_INSTALL_FIELD <> -1

then IFLD = INSTALL_FIELD

else IFLD = "n/a"

if IND_INSTALL_LOCATION<> -1

then ILOC = INSTALL_LOCATION

else ILOC = "n/a"

if IND_ADDITIONAL_INFO <> -1

then IADI = ADDITIONAL_INFO

else IADI = "n/a"

say left(macro,8)"!"left(parameter,24)"!" !!,

left(IFLD,24)"!"left(value,20)

if ROWNUM < 1

then say copies("-",8)"!"copies("-",24)"!" !!,

copies("-",24)"!"copies("-",20)

Address DSNREXX "EXECSQL" SQLSTMT

end

call code 0 100

sqlstmt = "CLOSE C101"

address DSNREXX "EXECSQL "sqlstmt;

call code 0 100

say "*** end ***"

/*********************************************************************/

/* Disconnect vom Subsystem */

/*********************************************************************/

Disconnect:

ADDRESS DSNREXX "DISCONNECT"

cc = RXSUBCOM("DELETE","DSNREXX","DSNREXX")

return

/*********************************************************************/

/* Error processing and termination */

/*********************************************************************/

SYNTAX:

say "DIS2INST: Syntax error."

say " [TSO] DIS2INST ssid options"

say " ssid .......: DB2 Subsystem ID"

say " criteria ...: criteria for DSNACCOX"

ERROR:

say ""

say "Current enviroment:" jobnamestep source_host_env,

source_call_type source_uc_exec_name source_hostdd,

source_hostdsn source_nf_exec_name source_initial_cmd_env

If Sysvar(SYSISPF) = "NOT ACTIVE"

then do

say "REXX procedure DIS2INST ends with U0001."

address ATTACH "XMPABND"

exit 0

end

say "REXX procedure DIS2INST ends with RC=20."

exit 20

/*********************************************************************/

/* Check SQLCODE's allowed */

/*********************************************************************/

Code:

arg codes

allowed = "NO"

do ci=1 to words(codes)

if sqlcode = word(codes,ci)

then allowed = "YES"

end

if allowed = "NO"

then do

say "CONNECT: SQL Error with SQLCODE="sqlcode

call TIAR sqlcode

say " "sqlstmt

call Disconnect

signal error

end

return

/*********************************************************************/

/* process line output, hide if value contains no information */

/*********************************************************************/

Line: arg lval ltext

lval = trunc(lval)

if lval > 0

then say right(lval,20)" "ltext

return

/*********************************************************************/

/* Retrieve some internal information from z/OS control blocks */

/*********************************************************************/

GET_JOBNAME: Procedure

/* all fields are part of general use prog'ing interface */

PSATOLD = '21C'X

TCB = C2D(STORAGE(C2X(PSATOLD),4))

TIOT = C2D(STORAGE(D2X(TCB+12),4))

TIOCNJOB = STRIP(STORAGE(D2X(TIOT),8))

TIOCSTEP = STRIP(STORAGE(D2X(TIOT+8),8))

Return TIOCNJOB TIOCSTEP

/*********************************************************************/

/* Display SQL Commmunication area using DSNTIAR */

/*********************************************************************/

TIAR: arg sqlcode

sqlc = d2x(sqlcode,8)

sqlc = x2c(sqlc)

sqlca = 'sqlca '

sqlca = sqlca !! x2c(00000088)

sqlca = sqlca !! sqlc

sqlca = sqlca !! x2c(0000)

sqlca = sqlca !! copies(' ',78)

sqlca = sqlca !! copies(x2c(00),24)

sqlca = sqlca !! copies(' ',16)

tiar_msg = x2c(0190)copies(' ',400)

text_len = x2c(00000050) ;

/***************************************************************/

/* Extract Message from Return Area */

/***************************************************************/

ADDRESS ATTCHPGM 'DSNTIAR SQLCA TIAR_MSG TEXT_LEN'

say substr(tiar_msg,4,400);

/***************************************************************/

/* Extract Message from Return Area */

/***************************************************************/

len = c2d(substr(rtrnarea,5,2))

/***************************************************************/

/* Extract Message from Return Area */

/***************************************************************/

say " SQLCODE="sqlcode

say " SQLERRMC="sqlerrmc

say " SQLWARN5="sqlwarn.5

say " SQLERRP ="sqlerrp

say " SQLSTATE="sqlstate

return

Look here for the XMPABND module.

Example:

 ###############################################################################
 # DIS2INST V1.0 - DSNHDECP DB2 for z/OS installation values 11/05/21 11:02:56 #
 ###############################################################################

 Executing without arguments, setting default DB2 subsystem id and criteria.
 MACRO   !PARAMETER               !INSTALL_FIELD           !VALUE              
 --------!------------------------!------------------------!--------------------
 DSN6SYSP!AUDITST                 !AUDIT TRACE             !11110000000000000000
 DSN6SYSP!CONDBAT                 !MAX REMOTE CONNECTED    !0000001000          
 DSN6SYSP!CTHREAD                 !MAX USERS               !00100              
 DSN6SYSP!DLDFREQ                 !LEVELID UPDATE FREQUENCY!00005              
 DSN6SYSP!PCLOSEN                 !RO SWITCH CHKPTS        !00005              
 DSN6SYSP!IDBACK                  !MAX BATCH CONNECT       !00100              
 DSN6SYSP!IDFORE                  !MAX TSO CONNECT         !00100              
 DSN6SYSP!CHKFREQ                 !CHECKPOINT FREQ         !0000500000          
 DSN6SYSP!MON                     !MONITOR TRACE           !00000000            
 DSN6SYSP!MONSIZE                 !MONITOR SIZE            !0000262144          
 DSN6SYSP!SYNCVAL                 !STATISTICS SYNC         !NO                  
 DSN6SYSP!RLFAUTH                 !RESOURCE AUTHID         !SYSIBM              
 DSN6SYSP!RLF                     !RLF AUTO START          !YES                
 DSN6SYSP!RLFERR                  !RLST ACCESS ERROR       !NOLIMIT            
 DSN6SYSP!RLFTBL                  !RLST NAME SUFFIX        !01                  
 DSN6SYSP!MAXDBAT                 !MAX REMOTE ACTIVE       !00050              
 DSN6SYSP!DSSTIME                 !DATASET STATS TIME      !00015              
 DSN6SYSP!EXTSEC                  !EXTENDED SECURITY       !YES                
 DSN6SYSP!SMFACCT                 !SMF ACCOUNTING          !11100000000000000000
 DSN6SYSP!SMFSTAT                 !SMF STATISTICS          !11101100000000000000
 DSN6SYSP!ROUTCDE                 !WTO ROUTE CODES         !1000000000000000    
 DSN6SYSP!STORMXAB                !MAX ABEND COUNT         !00000              
 DSN6SYSP!STORTIME                !TIMEOUT VALUE           !00180              
 DSN6SYSP!STATIME                 !STATISTICS TIME         !00005              
 DSN6SYSP!TRACLOC                 !                        !00016              
 DSN6SYSP!PCLOSET                 !RO SWITCH TIME          !00010              
 DSN6SYSP!TRACSTR                 !TRACE AUTO START        !00000000000000000000
 DSN6SYSP!TRACTBL                 !TRACE SIZE              !00016              
 DSN6SYSP!URCHKTH                 !UR CHECK FREQ           !000                
 DSN6SYSP!WLMENV                  !WLM ENVIRONMENT         !DB2TWLM1            
 DSN6SYSP!LOBVALA                 !USER LOB VALUE STG      !0000010240          
 DSN6SYSP!LOBVALS                 !SYSTEM LOB VAL STG      !0000002048          
 DSN6SYSP!XMLVALA                 !USER XML VALUE STG      !0002097152          
 DSN6SYSP!XMLVALS                 !SYSTEM XML VAL STG      !0000002048          
 DSN6SYSP!LOGAPSTG                !LOG APPLY STORAGE       !100                
 DSN6SYSP!PTASKROL                !                        !YES                
 DSN6SYSP!EXTRAREQ                !EXTRA BLOCKS REQ        !00100              
 DSN6SYSP!EXTRASRV                !EXTRA BLOCKS SRV        !00100              
 DSN6SYSP!TBSBPOOL                !DEFAULT BUFFER POOL FOR !BP10                
 DSN6SYSP!IDXBPOOL                !DEFAULT BUFFER POOL FOR !BP11                
 DSN6SYSP!LBACKOUT                !LIMIT BACKOUT           !AUTO                
 DSN6SYSP!BACKODUR                !BACKOUT DURATION        !005                
 DSN6SYSP!URLGWTH                 !UR LOG WRITE CHECK      !0000000000          
 DSN6SYSP!UIFCIDS                 !UNICODE IFCIDS          !NO                  
 DSN6SYSP!ACCUMACC                !DDF-RRSAF ACCUM         !00010              
 DSN6SYSP!ACCUMUID                !AGGREGATION FIELDS      !00000              
 DSN6SYSP!TSQTY                   !TABLE SPACE ALLOCATION  !0000000048          
 DSN6SYSP!IXQTY                   !INDEX SPACE ALLOCATION  !0000000048          
 DSN6SYSP!DSVCI                   !VARY DS CONTROL INTERVAL!YES                
 DSN6SYSP!MGEXTSZ                 !OPTIMIZE EXTENT SIZING  !YES                
 DSN6SYSP!SMF89                   !                        !NO                  
 DSN6SYSP!OTC_LICENSE             !                        !NOT_USED            
 DSN6SYSP!MAXOFILR                !MAX OPEN FILE REFS      !0000000100          
 DSN6SYSP!TBSBP8K                 !DEFAULT  8-KB BUFFER POO!BP8K0              
 DSN6SYSP!TBSBP16K                !DEFAULT 16-KB BUFFER POO!BP16K0              
 DSN6SYSP!TBSBP32K                !DEFAULT 32-KB BUFFER POO!BP32K              
 DSN6SYSP!TBSBPLOB                !DEFAULT BUFFER POOL FOR !BP1                
 DSN6SYSP!TBSBPXML                !DEFAULT BUFFER POOL FOR !BP16K0              
 DSN6SYSP!IMPDSDEF                !DEFINE DATA SETS        !YES                
 DSN6SYSP!IMPTSCMP                !USE DATA COMPRESSION    !YES                
 DSN6LOGP!TWOACTV                 !NUMBER OF COPIES        !2                  
 DSN6LOGP!OFFLOAD                 !                        !YES                
 DSN6LOGP!TWOBSDS                 !                        !2                  
 DSN6LOGP!TWOARCH                 !NUMBER OF COPIES        !1                  
 DSN6LOGP!MAXARCH                 !RECORDING MAX           !0000010000          
 DSN6LOGP!DEALLCT                 !DEALLOC PERIOD          !00000:00000        
 DSN6LOGP!MAXRTU                  !READ TAPE UNITS         !00002              
 DSN6LOGP!OUTBUFF                 !OUTPUT BUFFER           !0000004000          
 DSN6LOGP!ARC2FRST                !READ COPY2 ARCHIVE      !NO                  
 DSN6ARVP!BLKSIZE                 !BLOCK SIZE              !0000028672          
 DSN6ARVP!CATALOG                 !CATALOG DATA            !YES                
 DSN6ARVP!ALCUNIT                 !ALLOCATION UNITS        !CYL                
 DSN6ARVP!PROTECT                 !ARCHIVE LOG RACF        !NO                  
 DSN6ARVP!ARCWTOR                 !WRITE TO OPER           !NO                  
 DSN6ARVP!COMPACT                 !COMPACT DATA            !NO                  
 DSN6ARVP!TSTAMP                  !TIMESTAMP ARCHIVES      !YES                
 DSN6ARVP!QUIESCE                 !QUIESCE PERIOD          !00060              
 DSN6ARVP!ARCRETN                 !RETENTION PERIOD        !00015              
 DSN6ARVP!ARCPFX1                 !ARCH LOG 1 PREFIX       !DSNDB2T.DB2T.ARC1  
 DSN6ARVP!ARCPFX2                 !ARCH LOG 2 PREFIX       !DSNDB2T.DB2T.ARC2  
 DSN6ARVP!PRIQTY                  !PRIMARY QUANTITY        !0000001000          
 DSN6ARVP!SECQTY                  !SECONDARY QTY           !0000000010          
 DSN6ARVP!UNIT                    !DEVICE TYPE 1           !TAPE              
 DSN6ARVP!UNIT2                   !DEVICE TYPE 2           !TAPE              
 DSN6ARVP!SVOLARC                 !SINGLE VOLUME           !NO                  
 DSN6ARVP!ARCWRTC                 !WTOR ROUTE CODE         !1011000000000000    
 DSN6ARVP!MSVGP                   !                        !                    
 DSN6ARVP!MSVGP2                  !                        !                    
 DSN6SPRM!ABIND                   !AUTO BIND               !YES                
 DSN6SPRM!SYSADM2                 !SYSTEM ADMIN 2          !DB2IUSR              
 DSN6SPRM!AUTHCACH                !PLAN AUTH CACHE         !03072              
 DSN6SPRM!AUTH                    !USE PROTECTION          !YES                
 DSN6SPRM!BMPTOUT                 !IMS BMP TIMEOUT         !00004              
 DSN6SPRM!LEMAX                   !MAXIMUM LE TOKENS       !00020              
 DSN6SPRM!BINDNV                  !BIND NEW PACKAGE        !BINDADD            
 DSN6SPRM!CDSSRDEF                !CURRENT DEGREE          !1                  
 DSN6SPRM!DBCHK                   !                        !NO                  
 DSN6SPRM!DEFLTID                 !UNKNOWN AUTHID          !IBMUSER            
 DSN6SPRM!CHGDC AND EDPROP        !DPROP SUPPORT           !1                  
 DSN6SPRM!DECDIV3                 !MINIMUM DIVIDE SCALE    !NO                  
 DSN6SPRM!DLITOUT                 !DLI BATCH TIMEOUT       !00006              
 DSN6SPRM!DSMAX                   !MAXIMUM OPEN DATA SETS  !0000020000          
 DSN6SPRM!EDMPOOL                 !EDMPOOL STORAGE SIZE    !0000005000          
 DSN6SPRM!RECALLD                 !RECALL DELAY            !00120              
 DSN6SPRM!RECALL                  !RECALL DATA BASE        !YES                
 DSN6SPRM!IRLMAUT                 !AUTO START              !YES                
 DSN6SPRM!ABEXP                   !EXPLAIN PROCESSING      !YES                
 DSN6SPRM!IRLMPRC                 !PROC NAME               !DB2TIRLM            
 DSN6SPRM!IRLMSID                 !SUBSYSTEM NAME          !IIBA                
 DSN6SPRM!IRLMSWT                 !TIME TO AUTO START      !0000000300          
 DSN6SPRM!NUMLKTS                 !LOCKS PER TABLE(SPACE)  !0000001000          
 DSN6SPRM!NUMLKUS                 !LOCKS PER USER          !0000020000          
 DSN6SPRM!HOPAUTH                 !AUTH AT HOP SITE        !BOTH                
 DSN6SPRM!SEQCACH                 !SEQUENTIAL CACHE        !BYPASS              
 DSN6SPRM!RRULOCK                 !U LOCK FOR RR OR RS     !NO                  
 DSN6SPRM!DESCSTAT                !DESCRIBE FOR STATIC     !YES                
 DSN6SPRM!SEQPRES                 !UTILITY CACHE OPTION    !NO                  
 DSN6SPRM!CACHEDYN                !CACHE DYNAMIC SQL       !YES                
 DSN6SPRM!RETLWAIT                !RETAINED LOCK TIMEOUT   !00000              
 DSN6SPRM!CACHERAC                !ROUTINE AUTH CACHE      !0000102400          
 DSN6SPRM!CONTSTOR                !CONTRACT THREAD STG     !NO                  
 DSN6SPRM!MAXKEEPD                !MAX KEPT DYN STMTS      !0000000100          
 DSN6SPRM!PROTOFF                 !                        !NO                  
 DSN6SPRM!AEXITLIM                !AUTH EXIT LIMIT         !00010              
 DSN6SPRM!RETVLCFK                !VARCHAR FROM INDEX      !NO                  
 DSN6SPRM!OJPERFEH                !                        !YES                
 DSN6SPRM!SYSOPR1                 !SYSTEM OPERATOR 1       !SYSOPR              
 DSN6SPRM!SYSOPR2                 !SYSTEM OPERATOR 2       !DB2IUSR              
 DSN6SPRM!CACHEPAC                !PACKAGE AUTH CACHE      !0000102400          
 DSN6SPRM!PARA_EFF                !                        !00050              
 DSN6SPRM!PARAMDEG                !MAX DEGREE              !0000000001          
 DSN6SPRM!PARTKEYU                !UPDATE PART KEY COLS    !YES                
 DSN6SPRM!STATHIST                !STATISTICS HISTORY      !NONE                
 DSN6SPRM!RGFNMPRT                !APPL REGISTRATION TABLE !DSN_REGISTER_APPL  
 DSN6SPRM!RGFCOLID                !REGISTRATION OWNER      !DSNRGCOL            
 DSN6SPRM!RGFESCP                 !ART-ORT ESCAPE CHAR     !                    
 DSN6SPRM!RGFINSTL                !INSTALL DDCTRL SUPT     !NO                  
 DSN6SPRM!RGFDEDPL                !CONTROL ALL APPLICATIONS!NO                  
 DSN6SPRM!RGFFULLQ                !REQUIRE FULL NAMES      !YES                
 DSN6SPRM!RGFDEFLT                !UNREGISTERED DDL DEFAULT!ACCEPT              
 DSN6SPRM!RGFDBNAM                !REGISTRATION DATABASE   !DSNRGFDB            
 DSN6SPRM!RGFNMORT                !OBJT REGISTRATION TABLE !DSN_REGISTER_OBJT  
 DSN6SPRM!MAXRBLK                 !RID POOL SIZE           !0000008000          
 DSN6SPRM!MINRBLK                 !                        !0000000001          
 DSN6SPRM!SYSADM                  !SYSTEM ADMIN 1          !DB2IUSR              
 DSN6SPRM!SRTPOOL                 !SORT POOL SIZE          !0000002000          
 DSN6SPRM!IRLMRWT                 !RESOURCE TIMEOUT        !0000000060          
 DSN6SPRM!SITETYP                 !SITE TYPE               !LOCALSITE          
 DSN6SPRM!UTIMOUT                 !UTILITY TIMEOUT         !00006              
 DSN6SPRM!XLKUPDLT                !X LOCK FOR SEARCHED U OR!NO                  
 DSN6SPRM!OPTHINTS                !OPTIMIZATION HINTS      !NO                  
 DSN6SPRM!TRKRSITE                !TRACKER SITE            !NO                  
 DSN6SPRM!EDMBFIT                 !LARGE EDM BETTER FIT    !YES                
 DSN6SPRM!NPGTHRSH                !                        !0000000000          
 DSN6SPRM!SJTABLES                !                        !00010              
 DSN6SPRM!STARJOIN                !STAR JOIN QUERIES       !DISABLE            
 DSN6SPRM!SMSDCFL                 !                        !                    
 DSN6SPRM!SMSDCIX                 !                        !                    
 DSN6SPRM!DBACRVW                 !DBADM CREATE AUTH       !YES                
 DSN6SPRM!SUPERRS                 !SUPPRESS SOFT ERRORS    !YES                
 DSN6SPRM!STATROLL                !STATISTICS ROLLUP       !NO                  
 DSN6SPRM!MINSTOR                 !MANAGE THREAD STORAGE   !YES                
 DSN6SPRM!EVALUNC                 !EVALUATE UNCOMMITTED    !NO                  
 DSN6SPRM!STATSINT                !REAL TIME STATS         !00030              
 DSN6SPRM!MINDVSCL                !                        !NONE                
 DSN6SPRM!MXTBJOIN                !                        !00225              
 DSN6SPRM!EDMDBDC                 !EDM DBD CACHE           !0000005000          
 DSN6SPRM!EDMSTMTC                !EDM STATEMENT CACHE     !0000005000          
 DSN6SPRM!EDM_SKELETON_POOL       !EDM SKELETON POOL SIZE  !0000025000          
 DSN6SPRM!REFSHAGE                !CURRENT REFRESH AGE     !0                  
 DSN6SPRM!MAINTYPE                !CURRENT MAINT TYPES     !SYSTEM              
 DSN6SPRM!MAX_OPT_STOR            !                        !00040              
 DSN6SPRM!MAX_OPT_CPU             !                        !00100              
 DSN6SPRM!LRDRTHLD                !LONG-RUNNING READER     !00000              
 DSN6SPRM!VOLTDEVT                !TEMP DS UNIT NAME       !SYSDA              
 DSN6SPRM!DISABSCL                !                        !NO                  
 DSN6SPRM!SKIPUNCI                !SKIP UNCOMM INSERTS     !NO                  
 DSN6SPRM!MXQBCE                  !                        !0000001023          
 DSN6SPRM!PADIX                   !PAD INDEXES BY DEFAULT  !NO                  
 DSN6SPRM!SMGE                    !                        !NO                  
 DSN6SPRM!SECLCACH                !                        !00255              
 DSN6SPRM!MXDTCACH                !MAX DATA CACHING        !00020              
 DSN6SPRM!MAX_NUM_CUR             !MAX OPEN CURSORS        !0000000500          
 DSN6SPRM!MAX_ST_PROC             !MAX STORED PROCS        !0000002000          
 DSN6SPRM!INLISTP                 !                        !0000000050          
 DSN6SPRM!MAXTEMPS                !MAX TEMP STGÖAGENT      !0000000000          
 DSN6SPRM!WFDBSEP                 !                        !NO                  
 DSN6SPRM!XMLTABJPD               !                        !NO                  
 DSN6SPRM!SYSTEM_LEVEL_BACKUPS    !SYSTEM-LEVEL BACKUPS    !NO                  
 DSN6SPRM!RESTORE_RECOVER_FROMDUMP!RESTOREÖRECOVER         !NO                  
 DSN6SPRM!UTILS_DUMP_CLASS_NAME   !DUMP CLASS NAME         !                    
 DSN6SPRM!RESTORE_TAPEUNITS       !MAXIMUM TAPE UNITS      !NOLIMIT            
 DSN6SPRM!UTIL_TEMP_STORCLAS      !                        !                    
 DSN6SPRM!OPTXQB                  !                        !ON                  
 DSN6SPRM!RPITWC                  !                        !YES                
 DSN6SPRM!UNION_COLNAME_7         !                        !NO                  
 DSN6SPRM!SJMISSKY                !                        !OFF                
 DSN6SPRM!COMCRIT                 !                        !NO                  
 DSN6SPRM!PTCDIO                  !                        !OFF                
 DSN6SPRM!OPTIXIO                 !                        !ON                  
 DSN6SPRM!STATCLUS                !STATISTICS CLUSTERING   !ENHANCED            
 DSN6SPRM!CACHEDYN_FREELOCAL      !                        !00001              
 DSN6SPRM!UTSORTAL                !UT SORT DATA ALLOCATION !YES                
 DSN6SPRM!IGNSORTN                !IGNORE SORTNUM STMT     !NO                  
 DSN6SPRM!DB2SORT                 !                        !DISABLE            
 DSN6SPRM!PLANMGMT                !                        !OFF                
 DSN6SPRM!MORE_UNION_DISTRIBUTION !                        !OFF                
 DSN6SPRM!MAX_CONCURRENT_PKG_OPS  !                        !00010              
 DSN6SPRM!ADMTPROC                !                        !                    
 DSN6SPRM!HONOR_KEEPDICTIONARY    !                        !NO                  
 DSN6SPRM!MAX_UTIL_PARTS          !                        !00254              
 DSN6SPRM!OPTIXOPREF              !                        !ON                  
 DSN6SPRM!OPTJBPR                 !                        !ON                  
 DSN6SPRM!ZOSMETRICS              !                        !YES                
 DSN6SPRM!OPTJBPL                 !                        !ON                  
 DSN6SPRM!OPTIOWGT                !                        !ENABLE              
 DSN6SPRM!RRF                     !                        !DISABLE            
 DSN6SPRM!OPTOFNRE                !                        !ENABLE              
 DSN6SPRM!EN_PJSJ                 !                        !OFF                
 DSN6SPRM!ACCEL                   !                        !NO                  
 DSN6SPRM!PREDPRUNE               !                        !NO                  
 DSN6SPRM!COMPRESS_SPT01          !                        !YES                
 DSN6SPRM!REORG_IGNORE_FREESPACE  !                        !NO                  
 DSN6SPRM!OPTOIRCPF               !                        !DISABLE            
 DSN6SPRM!OPTHYBCST               !                        !OFF                
 DSN6SPRM!DRDA_RESOLVE_ALIAS      !                        !NO                  
 DSN6SPRM!PREVALKEEP              !                        !NO                  
 DSN6SPRM!CHECK_FASTREPLICATION   !                        !PREFERRED          
 DSN6SPRM!FLASHCOPY_PPRC          !                        !                    
 DSN6SPRM!SIMULATED_CPU_COUNT     !                        !OFF                
 DSN6SPRM!SIMULATED_CPU_SPEED     !                        !OFF                
 DSN6SPRM!CATALOG                 !CATALOG ALIAS           !DSNDB2T            
 DSN6SPRM!RESTART DEF             !RESTART OR DEFER        !RESTART            
 DSN6SPRM!ALL-DBNAME              !DBSTARTX                !ALL                
 DSN6FAC !CMTSTAT                 !DDF THREADS             !INACTIVE            
 DSN6FAC !TCPALVER                !TCP IP ALREADY VERIFIED !NO                  
 DSN6FAC !PRGSTRIN                !                        !ENABLE              
 DSN6FAC !RESYNC                  !RESYNC INTERVAL         !00002              
 DSN6FAC !RLFERRD                 !RLST ACCESS ERROR       !NOLIMIT            
 DSN6FAC !DDF                     !DDF STARTUP OPTION      !AUTO                
 DSN6FAC !IDTHTOIN                !IDLE THREAD TIMEOUT     !03600              
 DSN6FAC !MAXTYPE1                !MAX TYPE1 INACTIVE THREA!0000000000          
 DSN6FAC !TCPKPALV                !TCPIP KEEPALIVE         !00120              
 DSN6FAC !POOLINAC                !POOL THREAD TIMEOUT     !00180              
 DSN6FAC !SQLINTRP                !                        !ENABLE              
 DSN6FAC !PRIVATE_PROTOCOL        !                        !YES                
 DSN6GRP !ASSIST                  !ASSISTANT               !NO                  
 DSN6GRP !COORDNTR                !COORDINATOR             !NO                  
 DSN6GRP !IMMEDWRI                !IMMEDIATE WRITE         !NO                  
 DSN6GRP !DSHARE                  !DATA SHARING FUNCTION   !NO                  
 DSN6GRP !MEMBNAME                !MEMBER NAME             !DSN1                
 DSN6GRP !GRPNAME                 !GROUP NAME              !DSNCAT              
 DSN6GRP !RANDOMATT               !                        !YES                
 DSNHDECP!DB2SUPLD                !                        !NO                  
 DSNHDECP!DEFLANG                 !LANGUAGE DEFAULT        !IBMCOB              
 DSNHDECP!DECIMAL                 !DECIMAL POINT IS        !PERIOD              
 DSNHDECP!DELIM                   !STRING DELIMITER        !DEFAULT            
 DSNHDECP!SQLDELI                 !SQL STRING DELIMITER    !DEFAULT            
 DSNHDECP!DSQLDELI                !DIST SQL STR DELIMITER  !APOST              
 DSNHDECP!MIXED                   !MIXED DATA              !NO                  
 DSNHDECP!SCCSID                  !EBCDIC CCSID            !00273              
 DSNHDECP!MCCSID                  !EBCDIC CCSID            !65534              
 DSNHDECP!GCCSID                  !EBCDIC CCSID            !65534              
 DSNHDECP!ASCCSID                 !ASCII CCSID             !05348              
 DSNHDECP!AMCCSID                 !ASCII CCSID             !65534              
 DSNHDECP!AGCCSID                 !ASCII CCSID             !65534              
 DSNHDECP!USCCSID                 !UNICODE CCSID           !00367              
 DSNHDECP!UMCCSID                 !UNICODE CCSID           !01208              
 DSNHDECP!UGCCSID                 !UNICODE CCSID           !01200              
 DSNHDECP!ENSCHEME                !DEF ENCODING SCHEME     !EBCDIC              
 DSNHDECP!APPENSCH                !APPLICATION ENCODING    !EBCDIC              
1DSNHDECP!NEWFUN                  !                        !YES                
 DSNHDECP!DATE                    !DATE FORMAT             !ISO                
 DSNHDECP!TIME                    !TIME FORMAT             !ISO                
 DSNHDECP!DATELEN                 !LOCAL DATE LENGTH       !000                
 DSNHDECP!TIMELEN                 !LOCAL TIME LENGTH       !000                
 DSNHDECP!STDSQL                  !STD SQL LANGUAGE        !NO                  
 DSNHDECP!CHARSET                 !                        !ALPHANUM            
 DSNHDECP!SSID                    !SUBSYSTEM NAME          !DB2T                
 DSNHDECP!DECARTH                 !DECIMAL ARITHMETIC      !DEC15              
 DSNHDECP!DYNRULS                 !USE FOR DYNAMICRULES    !YES                
 DSNHDECP!LC_CTYPE                !LOCALE LC_CTYPE         !                    
 DSNHDECP!PADNTSTR                !PAD NUL-TERMINATED      !NO                  
 DSNHDECP!DEF_DECFLOAT_ROUND_MODE !DECFLOAT ROUNDING MODE  !ROUND_HALF_EVEN    
 DB2TIRLM!PC SPECIFIED            !                        !YES                
 DB2TIRLM!WAIT TIME FOR LOCAL DEAD!DEADLOCK TIME           !00001              
 DB2TIRLM!LOCAL CYCLES PER GLOBAL !DEADLOCK CYCLE          !00001              
 DB2TIRLM!TIMEOUT INTERVAL        !                        !0000000060          
 DB2TIRLM!IRLM MAXIMUM CSA USAGE A!                        !0000000000          
 DB2TIRLM!SYSTEM LOCK TABLE HASH E!                        !0000000000          
 DB2TIRLM!PENDING NUMBER OF HASH E!                        !0000000000          
 DB2TIRLM!SYSTEM LOCK TABLE LIST E!                        !0000000000          
 *** end ***

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

###############################################################################

# DIS2INST V1.0 - DSNHDECP DB2 for z/OS installation values 11/05/21 11:02:56 #

###############################################################################

Executing without arguments, setting default DB2 subsystem id and criteria.

MACRO !PARAMETER !INSTALL_FIELD !VALUE

--------!------------------------!------------------------!--------------------

DSN6SYSP!AUDITST !AUDIT TRACE !11110000000000000000

DSN6SYSP!CONDBAT !MAX REMOTE CONNECTED !0000001000

DSN6SYSP!CTHREAD !MAX USERS !00100

DSN6SYSP!DLDFREQ !LEVELID UPDATE FREQUENCY!00005

DSN6SYSP!PCLOSEN !RO SWITCH CHKPTS !00005

DSN6SYSP!IDBACK !MAX BATCH CONNECT !00100

DSN6SYSP!IDFORE !MAX TSO CONNECT !00100

DSN6SYSP!CHKFREQ !CHECKPOINT FREQ !0000500000

DSN6SYSP!MON !MONITOR TRACE !00000000

DSN6SYSP!MONSIZE !MONITOR SIZE !0000262144

DSN6SYSP!SYNCVAL !STATISTICS SYNC !NO

DSN6SYSP!RLFAUTH !RESOURCE AUTHID !SYSIBM

DSN6SYSP!RLF !RLF AUTO START !YES

DSN6SYSP!RLFERR !RLST ACCESS ERROR !NOLIMIT

DSN6SYSP!RLFTBL !RLST NAME SUFFIX !01

DSN6SYSP!MAXDBAT !MAX REMOTE ACTIVE !00050

DSN6SYSP!DSSTIME !DATASET STATS TIME !00015

DSN6SYSP!EXTSEC !EXTENDED SECURITY !YES

DSN6SYSP!SMFACCT !SMF ACCOUNTING !11100000000000000000

DSN6SYSP!SMFSTAT !SMF STATISTICS !11101100000000000000

DSN6SYSP!ROUTCDE !WTO ROUTE CODES !1000000000000000

DSN6SYSP!STORMXAB !MAX ABEND COUNT !00000

DSN6SYSP!STORTIME !TIMEOUT VALUE !00180

DSN6SYSP!STATIME !STATISTICS TIME !00005

DSN6SYSP!TRACLOC ! !00016

DSN6SYSP!PCLOSET !RO SWITCH TIME !00010

DSN6SYSP!TRACSTR !TRACE AUTO START !00000000000000000000

DSN6SYSP!TRACTBL !TRACE SIZE !00016

DSN6SYSP!URCHKTH !UR CHECK FREQ !000

DSN6SYSP!WLMENV !WLM ENVIRONMENT !DB2TWLM1

DSN6SYSP!LOBVALA !USER LOB VALUE STG !0000010240

DSN6SYSP!LOBVALS !SYSTEM LOB VAL STG !0000002048

DSN6SYSP!XMLVALA !USER XML VALUE STG !0002097152

DSN6SYSP!XMLVALS !SYSTEM XML VAL STG !0000002048

DSN6SYSP!LOGAPSTG !LOG APPLY STORAGE !100

DSN6SYSP!PTASKROL ! !YES

DSN6SYSP!EXTRAREQ !EXTRA BLOCKS REQ !00100

DSN6SYSP!EXTRASRV !EXTRA BLOCKS SRV !00100

DSN6SYSP!TBSBPOOL !DEFAULT BUFFER POOL FOR !BP10

DSN6SYSP!IDXBPOOL !DEFAULT BUFFER POOL FOR !BP11

DSN6SYSP!LBACKOUT !LIMIT BACKOUT !AUTO

DSN6SYSP!BACKODUR !BACKOUT DURATION !005

DSN6SYSP!URLGWTH !UR LOG WRITE CHECK !0000000000

DSN6SYSP!UIFCIDS !UNICODE IFCIDS !NO

DSN6SYSP!ACCUMACC !DDF-RRSAF ACCUM !00010

DSN6SYSP!ACCUMUID !AGGREGATION FIELDS !00000

DSN6SYSP!TSQTY !TABLE SPACE ALLOCATION !0000000048

DSN6SYSP!IXQTY !INDEX SPACE ALLOCATION !0000000048

DSN6SYSP!DSVCI !VARY DS CONTROL INTERVAL!YES

DSN6SYSP!MGEXTSZ !OPTIMIZE EXTENT SIZING !YES

DSN6SYSP!SMF89 ! !NO

DSN6SYSP!OTC_LICENSE ! !NOT_USED

DSN6SYSP!MAXOFILR !MAX OPEN FILE REFS !0000000100

DSN6SYSP!TBSBP8K !DEFAULT 8-KB BUFFER POO!BP8K0

DSN6SYSP!TBSBP16K !DEFAULT 16-KB BUFFER POO!BP16K0

DSN6SYSP!TBSBP32K !DEFAULT 32-KB BUFFER POO!BP32K

DSN6SYSP!TBSBPLOB !DEFAULT BUFFER POOL FOR !BP1

DSN6SYSP!TBSBPXML !DEFAULT BUFFER POOL FOR !BP16K0

DSN6SYSP!IMPDSDEF !DEFINE DATA SETS !YES

DSN6SYSP!IMPTSCMP !USE DATA COMPRESSION !YES

DSN6LOGP!TWOACTV !NUMBER OF COPIES !2

DSN6LOGP!OFFLOAD ! !YES

DSN6LOGP!TWOBSDS ! !2

DSN6LOGP!TWOARCH !NUMBER OF COPIES !1

DSN6LOGP!MAXARCH !RECORDING MAX !0000010000

DSN6LOGP!DEALLCT !DEALLOC PERIOD !00000:00000

DSN6LOGP!MAXRTU !READ TAPE UNITS !00002

DSN6LOGP!OUTBUFF !OUTPUT BUFFER !0000004000

DSN6LOGP!ARC2FRST !READ COPY2 ARCHIVE !NO

DSN6ARVP!BLKSIZE !BLOCK SIZE !0000028672

DSN6ARVP!CATALOG !CATALOG DATA !YES

DSN6ARVP!ALCUNIT !ALLOCATION UNITS !CYL

DSN6ARVP!PROTECT !ARCHIVE LOG RACF !NO

DSN6ARVP!ARCWTOR !WRITE TO OPER !NO

DSN6ARVP!COMPACT !COMPACT DATA !NO

DSN6ARVP!TSTAMP !TIMESTAMP ARCHIVES !YES

DSN6ARVP!QUIESCE !QUIESCE PERIOD !00060

DSN6ARVP!ARCRETN !RETENTION PERIOD !00015

DSN6ARVP!ARCPFX1 !ARCH LOG 1 PREFIX !DSNDB2T.DB2T.ARC1

DSN6ARVP!ARCPFX2 !ARCH LOG 2 PREFIX !DSNDB2T.DB2T.ARC2

DSN6ARVP!PRIQTY !PRIMARY QUANTITY !0000001000

DSN6ARVP!SECQTY !SECONDARY QTY !0000000010

DSN6ARVP!UNIT !DEVICE TYPE 1 !TAPE

DSN6ARVP!UNIT2 !DEVICE TYPE 2 !TAPE

DSN6ARVP!SVOLARC !SINGLE VOLUME !NO

DSN6ARVP!ARCWRTC !WTOR ROUTE CODE !1011000000000000

DSN6ARVP!MSVGP ! !

DSN6ARVP!MSVGP2 ! !

DSN6SPRM!ABIND !AUTO BIND !YES

DSN6SPRM!SYSADM2 !SYSTEM ADMIN 2 !DB2IUSR

DSN6SPRM!AUTHCACH !PLAN AUTH CACHE !03072

DSN6SPRM!AUTH !USE PROTECTION !YES

DSN6SPRM!BMPTOUT !IMS BMP TIMEOUT !00004

DSN6SPRM!LEMAX !MAXIMUM LE TOKENS !00020

DSN6SPRM!BINDNV !BIND NEW PACKAGE !BINDADD

DSN6SPRM!CDSSRDEF !CURRENT DEGREE !1

DSN6SPRM!DBCHK ! !NO

DSN6SPRM!DEFLTID !UNKNOWN AUTHID !IBMUSER

DSN6SPRM!CHGDC AND EDPROP !DPROP SUPPORT !1

DSN6SPRM!DECDIV3 !MINIMUM DIVIDE SCALE !NO

DSN6SPRM!DLITOUT !DLI BATCH TIMEOUT !00006

DSN6SPRM!DSMAX !MAXIMUM OPEN DATA SETS !0000020000

DSN6SPRM!EDMPOOL !EDMPOOL STORAGE SIZE !0000005000

DSN6SPRM!RECALLD !RECALL DELAY !00120

DSN6SPRM!RECALL !RECALL DATA BASE !YES

DSN6SPRM!IRLMAUT !AUTO START !YES

DSN6SPRM!ABEXP !EXPLAIN PROCESSING !YES

DSN6SPRM!IRLMPRC !PROC NAME !DB2TIRLM

DSN6SPRM!IRLMSID !SUBSYSTEM NAME !IIBA

DSN6SPRM!IRLMSWT !TIME TO AUTO START !0000000300

DSN6SPRM!NUMLKTS !LOCKS PER TABLE(SPACE) !0000001000

DSN6SPRM!NUMLKUS !LOCKS PER USER !0000020000

DSN6SPRM!HOPAUTH !AUTH AT HOP SITE !BOTH

DSN6SPRM!SEQCACH !SEQUENTIAL CACHE !BYPASS

DSN6SPRM!RRULOCK !U LOCK FOR RR OR RS !NO

DSN6SPRM!DESCSTAT !DESCRIBE FOR STATIC !YES

DSN6SPRM!SEQPRES !UTILITY CACHE OPTION !NO

DSN6SPRM!CACHEDYN !CACHE DYNAMIC SQL !YES

DSN6SPRM!RETLWAIT !RETAINED LOCK TIMEOUT !00000

DSN6SPRM!CACHERAC !ROUTINE AUTH CACHE !0000102400

DSN6SPRM!CONTSTOR !CONTRACT THREAD STG !NO

DSN6SPRM!MAXKEEPD !MAX KEPT DYN STMTS !0000000100

DSN6SPRM!PROTOFF ! !NO

DSN6SPRM!AEXITLIM !AUTH EXIT LIMIT !00010

DSN6SPRM!RETVLCFK !VARCHAR FROM INDEX !NO

DSN6SPRM!OJPERFEH ! !YES

DSN6SPRM!SYSOPR1 !SYSTEM OPERATOR 1 !SYSOPR

DSN6SPRM!SYSOPR2 !SYSTEM OPERATOR 2 !DB2IUSR

DSN6SPRM!CACHEPAC !PACKAGE AUTH CACHE !0000102400

DSN6SPRM!PARA_EFF ! !00050

DSN6SPRM!PARAMDEG !MAX DEGREE !0000000001

DSN6SPRM!PARTKEYU !UPDATE PART KEY COLS !YES

DSN6SPRM!STATHIST !STATISTICS HISTORY !NONE

DSN6SPRM!RGFNMPRT !APPL REGISTRATION TABLE !DSN_REGISTER_APPL

DSN6SPRM!RGFCOLID !REGISTRATION OWNER !DSNRGCOL

DSN6SPRM!RGFESCP !ART-ORT ESCAPE CHAR !

DSN6SPRM!RGFINSTL !INSTALL DDCTRL SUPT !NO

DSN6SPRM!RGFDEDPL !CONTROL ALL APPLICATIONS!NO

DSN6SPRM!RGFFULLQ !REQUIRE FULL NAMES !YES

DSN6SPRM!RGFDEFLT !UNREGISTERED DDL DEFAULT!ACCEPT

DSN6SPRM!RGFDBNAM !REGISTRATION DATABASE !DSNRGFDB

DSN6SPRM!RGFNMORT !OBJT REGISTRATION TABLE !DSN_REGISTER_OBJT

DSN6SPRM!MAXRBLK !RID POOL SIZE !0000008000

DSN6SPRM!MINRBLK ! !0000000001

DSN6SPRM!SYSADM !SYSTEM ADMIN 1 !DB2IUSR

DSN6SPRM!SRTPOOL !SORT POOL SIZE !0000002000

DSN6SPRM!IRLMRWT !RESOURCE TIMEOUT !0000000060

DSN6SPRM!SITETYP !SITE TYPE !LOCALSITE

DSN6SPRM!UTIMOUT !UTILITY TIMEOUT !00006

DSN6SPRM!XLKUPDLT !X LOCK FOR SEARCHED U OR!NO

DSN6SPRM!OPTHINTS !OPTIMIZATION HINTS !NO

DSN6SPRM!TRKRSITE !TRACKER SITE !NO

DSN6SPRM!EDMBFIT !LARGE EDM BETTER FIT !YES

DSN6SPRM!NPGTHRSH ! !0000000000

DSN6SPRM!SJTABLES ! !00010

DSN6SPRM!STARJOIN !STAR JOIN QUERIES !DISABLE

DSN6SPRM!SMSDCFL ! !

DSN6SPRM!SMSDCIX ! !

DSN6SPRM!DBACRVW !DBADM CREATE AUTH !YES

DSN6SPRM!SUPERRS !SUPPRESS SOFT ERRORS !YES

DSN6SPRM!STATROLL !STATISTICS ROLLUP !NO

DSN6SPRM!MINSTOR !MANAGE THREAD STORAGE !YES

DSN6SPRM!EVALUNC !EVALUATE UNCOMMITTED !NO

DSN6SPRM!STATSINT !REAL TIME STATS !00030

DSN6SPRM!MINDVSCL ! !NONE

DSN6SPRM!MXTBJOIN ! !00225

DSN6SPRM!EDMDBDC !EDM DBD CACHE !0000005000

DSN6SPRM!EDMSTMTC !EDM STATEMENT CACHE !0000005000

DSN6SPRM!EDM_SKELETON_POOL !EDM SKELETON POOL SIZE !0000025000

DSN6SPRM!REFSHAGE !CURRENT REFRESH AGE !0

DSN6SPRM!MAINTYPE !CURRENT MAINT TYPES !SYSTEM

DSN6SPRM!MAX_OPT_STOR ! !00040

DSN6SPRM!MAX_OPT_CPU ! !00100

DSN6SPRM!LRDRTHLD !LONG-RUNNING READER !00000

DSN6SPRM!VOLTDEVT !TEMP DS UNIT NAME !SYSDA

DSN6SPRM!DISABSCL ! !NO

DSN6SPRM!SKIPUNCI !SKIP UNCOMM INSERTS !NO

DSN6SPRM!MXQBCE ! !0000001023

DSN6SPRM!PADIX !PAD INDEXES BY DEFAULT !NO

DSN6SPRM!SMGE ! !NO

DSN6SPRM!SECLCACH ! !00255

DSN6SPRM!MXDTCACH !MAX DATA CACHING !00020

DSN6SPRM!MAX_NUM_CUR !MAX OPEN CURSORS !0000000500

DSN6SPRM!MAX_ST_PROC !MAX STORED PROCS !0000002000

DSN6SPRM!INLISTP ! !0000000050

DSN6SPRM!MAXTEMPS !MAX TEMP STGÖAGENT !0000000000

DSN6SPRM!WFDBSEP ! !NO

DSN6SPRM!XMLTABJPD ! !NO

DSN6SPRM!SYSTEM_LEVEL_BACKUPS !SYSTEM-LEVEL BACKUPS !NO

DSN6SPRM!RESTORE_RECOVER_FROMDUMP!RESTOREÖRECOVER !NO

DSN6SPRM!UTILS_DUMP_CLASS_NAME !DUMP CLASS NAME !

DSN6SPRM!RESTORE_TAPEUNITS !MAXIMUM TAPE UNITS !NOLIMIT

DSN6SPRM!UTIL_TEMP_STORCLAS ! !

DSN6SPRM!OPTXQB ! !ON

DSN6SPRM!RPITWC ! !YES

DSN6SPRM!UNION_COLNAME_7 ! !NO

DSN6SPRM!SJMISSKY ! !OFF

DSN6SPRM!COMCRIT ! !NO

DSN6SPRM!PTCDIO ! !OFF

DSN6SPRM!OPTIXIO ! !ON

DSN6SPRM!STATCLUS !STATISTICS CLUSTERING !ENHANCED

DSN6SPRM!CACHEDYN_FREELOCAL ! !00001

DSN6SPRM!UTSORTAL !UT SORT DATA ALLOCATION !YES

DSN6SPRM!IGNSORTN !IGNORE SORTNUM STMT !NO

DSN6SPRM!DB2SORT ! !DISABLE

DSN6SPRM!PLANMGMT ! !OFF

DSN6SPRM!MORE_UNION_DISTRIBUTION ! !OFF

DSN6SPRM!MAX_CONCURRENT_PKG_OPS ! !00010

DSN6SPRM!ADMTPROC ! !

DSN6SPRM!HONOR_KEEPDICTIONARY ! !NO

DSN6SPRM!MAX_UTIL_PARTS ! !00254

DSN6SPRM!OPTIXOPREF ! !ON

DSN6SPRM!OPTJBPR ! !ON

DSN6SPRM!ZOSMETRICS ! !YES

DSN6SPRM!OPTJBPL ! !ON

DSN6SPRM!OPTIOWGT ! !ENABLE

DSN6SPRM!RRF ! !DISABLE

DSN6SPRM!OPTOFNRE ! !ENABLE

DSN6SPRM!EN_PJSJ ! !OFF

DSN6SPRM!ACCEL ! !NO

DSN6SPRM!PREDPRUNE ! !NO

DSN6SPRM!COMPRESS_SPT01 ! !YES

DSN6SPRM!REORG_IGNORE_FREESPACE ! !NO

DSN6SPRM!OPTOIRCPF ! !DISABLE

DSN6SPRM!OPTHYBCST ! !OFF

DSN6SPRM!DRDA_RESOLVE_ALIAS ! !NO

DSN6SPRM!PREVALKEEP ! !NO

DSN6SPRM!CHECK_FASTREPLICATION ! !PREFERRED

DSN6SPRM!FLASHCOPY_PPRC ! !

DSN6SPRM!SIMULATED_CPU_COUNT ! !OFF

DSN6SPRM!SIMULATED_CPU_SPEED ! !OFF

DSN6SPRM!CATALOG !CATALOG ALIAS !DSNDB2T

DSN6SPRM!RESTART DEF !RESTART OR DEFER !RESTART

DSN6SPRM!ALL-DBNAME !DBSTARTX !ALL

DSN6FAC !CMTSTAT !DDF THREADS !INACTIVE

DSN6FAC !TCPALVER !TCP IP ALREADY VERIFIED !NO

DSN6FAC !PRGSTRIN ! !ENABLE

DSN6FAC !RESYNC !RESYNC INTERVAL !00002

DSN6FAC !RLFERRD !RLST ACCESS ERROR !NOLIMIT

DSN6FAC !DDF !DDF STARTUP OPTION !AUTO

DSN6FAC !IDTHTOIN !IDLE THREAD TIMEOUT !03600

DSN6FAC !MAXTYPE1 !MAX TYPE1 INACTIVE THREA!0000000000

DSN6FAC !TCPKPALV !TCPIP KEEPALIVE !00120

DSN6FAC !POOLINAC !POOL THREAD TIMEOUT !00180

DSN6FAC !SQLINTRP ! !ENABLE

DSN6FAC !PRIVATE_PROTOCOL ! !YES

DSN6GRP !ASSIST !ASSISTANT !NO

DSN6GRP !COORDNTR !COORDINATOR !NO

DSN6GRP !IMMEDWRI !IMMEDIATE WRITE !NO

DSN6GRP !DSHARE !DATA SHARING FUNCTION !NO

DSN6GRP !MEMBNAME !MEMBER NAME !DSN1

DSN6GRP !GRPNAME !GROUP NAME !DSNCAT

DSN6GRP !RANDOMATT ! !YES

DSNHDECP!DB2SUPLD ! !NO

DSNHDECP!DEFLANG !LANGUAGE DEFAULT !IBMCOB

DSNHDECP!DECIMAL !DECIMAL POINT IS !PERIOD

DSNHDECP!DELIM !STRING DELIMITER !DEFAULT

DSNHDECP!SQLDELI !SQL STRING DELIMITER !DEFAULT

DSNHDECP!DSQLDELI !DIST SQL STR DELIMITER !APOST

DSNHDECP!MIXED !MIXED DATA !NO

DSNHDECP!SCCSID !EBCDIC CCSID !00273

DSNHDECP!MCCSID !EBCDIC CCSID !65534

DSNHDECP!GCCSID !EBCDIC CCSID !65534

DSNHDECP!ASCCSID !ASCII CCSID !05348

DSNHDECP!AMCCSID !ASCII CCSID !65534

DSNHDECP!AGCCSID !ASCII CCSID !65534

DSNHDECP!USCCSID !UNICODE CCSID !00367

DSNHDECP!UMCCSID !UNICODE CCSID !01208

DSNHDECP!UGCCSID !UNICODE CCSID !01200

DSNHDECP!ENSCHEME !DEF ENCODING SCHEME !EBCDIC

DSNHDECP!APPENSCH !APPLICATION ENCODING !EBCDIC

1DSNHDECP!NEWFUN ! !YES

DSNHDECP!DATE !DATE FORMAT !ISO

DSNHDECP!TIME !TIME FORMAT !ISO

DSNHDECP!DATELEN !LOCAL DATE LENGTH !000

DSNHDECP!TIMELEN !LOCAL TIME LENGTH !000

DSNHDECP!STDSQL !STD SQL LANGUAGE !NO

DSNHDECP!CHARSET ! !ALPHANUM

DSNHDECP!SSID !SUBSYSTEM NAME !DB2T

DSNHDECP!DECARTH !DECIMAL ARITHMETIC !DEC15

DSNHDECP!DYNRULS !USE FOR DYNAMICRULES !YES

DSNHDECP!LC_CTYPE !LOCALE LC_CTYPE !

DSNHDECP!PADNTSTR !PAD NUL-TERMINATED !NO

DSNHDECP!DEF_DECFLOAT_ROUND_MODE !DECFLOAT ROUNDING MODE !ROUND_HALF_EVEN

DB2TIRLM!PC SPECIFIED ! !YES

DB2TIRLM!WAIT TIME FOR LOCAL DEAD!DEADLOCK TIME !00001

DB2TIRLM!LOCAL CYCLES PER GLOBAL !DEADLOCK CYCLE !00001

DB2TIRLM!TIMEOUT INTERVAL ! !0000000060

DB2TIRLM!IRLM MAXIMUM CSA USAGE A! !0000000000

DB2TIRLM!SYSTEM LOCK TABLE HASH E! !0000000000

DB2TIRLM!PENDING NUMBER OF HASH E! !0000000000

DB2TIRLM!SYSTEM LOCK TABLE LIST E! !0000000000

*** end ***

Shell Script xmpSetInstance.sh – set DB2 environment

Activate requested DB2 environment:

Search for registered DB2 software version, instances, databases and execute the appropriate DB2 environment. The KSH shell script is quite useful on sites with more than one DB2 instance.

#!/bin/ksh
################################################################################
# xmpSetInstance.sh - set DB2 environment  
# ------------------------------------------------------------------------------
# Parameter: inst|db         	- instance or database name
#            LIST		- list databases
#
# System   : AIX
#            SunOS (Solaris)
#
# Comment  : On servers with more than one DB2 instance, user probably needs to 
#            switch between instances. The original IBM korn shell script 
#            ". db2dprofile" does not support switching between instance, but
#            concatenates instance settings.
#            How to determine and check DB2 profile registry for installed 
#            DB2 instances: < V9.1: /tmp/profiles.reg
#                           = V9.1: /opt/IBM/db2/V9.1/profiles.reg
#                           = V9.7: /var/[opt/]db2/global.reg
#            Since Version 9.1 DB2 provides the db2ls command which lists all
#            installed products and their installation paths. Using the most
#            current version of the db2greg command will list all installed
#            instances.
# ------------------------------------------------------------------------------
# Autor      G.Ruban /22.05.02  - erste Version V1.0
# Changes    G.Ruban /09.07.02  - V1.2: equal operand differs on platforms
#            G.Ruban /28.08.02  - V1.2.1: equal operand differs on platforms
#            G.Ruban /22.10.02  - V1.2.2: unset variables
#            G.Ruban /05.12.06  - V2.0: Some amendments necessary for DB2 V9.1
#            G.Ruban /14.05.07  - V2.1: use /usr/local/bin if db2ls not in path
#            G.Ruban /13.07.11  - V3.0: Upgrade to DB2 V9.7, profiles.reg not
#                                       longer supported 
#            G.Ruban /09.09.11  - V3.1: Problem new with AIX 6.1 solved         
################################################################################

#-------------------------------------------------------------------------------
# Init 
#-------------------------------------------------------------------------------
  PROG="xmpSetInstance.sh"
  VER="3.1"
  
  typeset -i dbi
  typeset -i t

  dbi=0

#-------------------------------------------------------------------------------
# Plaform-specific
#-------------------------------------------------------------------------------
  case `uname` in
    AIX)
      StringEQ="="
      ;;
    SunOS)
      StringEQ="=="
      ;;
   *)
    echo "$PROG: Unknown plaform, is not AIX or SunOS."
    exit 8
    ;;
  esac

#-------------------------------------------------------------------------------
# Check arguments 
#-------------------------------------------------------------------------------
  if [ $# -lt 1 -o "$1" $StringEQ "?" ] ; then
	echo "Requested syntax: $PROG [inst|db] [LIST]"
	echo "... where inst = name of local instance"
	echo "          db   = name of local database"
	echo "          LIST = also lists databases"
	echo "abends with RC=4"
 	exit 4
  fi

  typeset -u search=$1
  typeset -u list=$2

  if [ $# -eq 1 -a "$search" $StringEQ "LIST" ]; then
	search=""
	typeset -u list=$1
  fi

#-------------------------------------------------------------------------------
# Determine db2ls command to inquire installed software
#-------------------------------------------------------------------------------
  db2lspath=`which db2ls`
  rc=$?
  if [ $rc -ne 0 ] ; then
     echo ">>> $PROG: DB2 Utility db2ls not found in current path."
	 db2lspath="/usr/local/bin/db2ls"
	 echo ">>> Execution path set to $db2lspath."
  fi

#-------------------------------------------------------------------------------
# Inquire latest installation path of DB2 (provides 1 item)
#-------------------------------------------------------------------------------  
  db2sw=`$db2lspath | grep db2 | awk '{print $1}' | tail -1`
  rc=$?
  if [ $rc -ne 0 ] ; then
     echo "Neither DB2 V9.7 nor V9.1 found, continue searching older versions ..."
	 echo "abends with RC=16"
 	 exit 16	
  fi 
  if [ ! "$db2sw" ]; then
	echo "$0: DB2 command db2ls found but no installed DB2 products found!"
	echo "abends with RC=16"
 	exit 16	
  fi   

#-------------------------------------------------------------------------------
# For archive reasons only: Determination of profiles.reg and global.reg
# < V9.1: /tmp/profiles.reg
# = V9.1: /opt/IBM/db2/V9.1/profiles.reg
# = V9.7: /var/[opt/]db2/global.reg
# regfile=`find /var/db2/ -name 'global.reg' -type f -print;find /var/opt/db2/ -name 'global.reg' -type f -print;find /var/db2/ -name 'profiles.reg' -type f -print;find $db2ip -name 'profiles.reg' -type f -print`
#-------------------------------------------------------------------------------  

#-------------------------------------------------------------------------------
# Inquire all instances from DB2 registry, list instance name and path only
#-------------------------------------------------------------------------------  
  db2greg=$db2sw"/bin/db2greg -dump"
  #echo ">>> $db2greg"
  ilist=`$db2greg | awk -F, '/I,DB2/ {print $4":"$5}'`
  #echo ">>> $ilist"

#-------------------------------------------------------------------------------
# Provide information  
#-------------------------------------------------------------------------------
  echo $PROG " - V$VER"
  echo "======================================================="
  echo "Current User ................: "`id`
  echo "Current Date/Time ...........: "`date +%Y-%m-%d-%H.%M.%S`
  echo "System ......................: "`uname -n`
  echo "Required Instance or Database: $search"
  echo "List of instances and databases available at this server ..."

#-------------------------------------------------------------------------------
# Check for repeated invocation
#-------------------------------------------------------------------------------
  if [ "$lP_BANN" $StringEQ "$PROG" ]; then
	echo "-------------------------------------------------------"
	echo "Warning, $PROG invoked repeatedly!"
	echo "DB2 environment may not be set correctly!"
	echo "Use env command to check PATH, LIBPATH etc."
	echo "-------------------------------------------------------"
  fi
  
#-------------------------------------------------------------------------------
# Process all instances
#-------------------------------------------------------------------------------
  maxrc=0
  #echo ">>>$ilist<<<"
  for i in $ilist
  do
	#---------------------------------------------------------------------------
	# split instance name and instance path
	#---------------------------------------------------------------------------
	instname=${i%:*}
	instpath=${i#*:}
	#---------------------------------------------------------------------------
	# Check existance of db2profile script (for execution in next step)
	#---------------------------------------------------------------------------
	if [ -x "$instpath/db2profile" ]; then
		echo "- Instance / Home Directory  : $instname $instpath"	
		dbi=$dbi+1
		DBT[$dbi]="see below"
		DBN[$dbi]="list of databases"
		typeset -u DBI[$dbi]=$i
		DBP[$dbi]=$p

		#-----------------------------------------------------------
		# List database directory at instance
		#-----------------------------------------------------------
		dbl=`ksh -c ". $instpath/db2profile; db2 list db directory|egrep '(alias|type)'|cut -f2 -d'='; exit"`
		rc=$?
		#-----------------------------------------------------------
		# Store database names and types
		#-----------------------------------------------------------
		for db in $dbl
		do
			case $db in
			  Indirect) typeset -u DBT[$dbi]=$db;;
			  Remote)	typeset -u DBT[$dbi]=$db;;
			  *)		dbi=$dbi+1
						typeset -u DBN[$dbi]=$db
						typeset -u DBI[$dbi]=$instname
						DBP[$dbi]=$instpath;;
			esac
		done
	fi
  done

#-------------------------------------------------------------------------------
# Activate instance environment if instance name or database found
#-------------------------------------------------------------------------------
  if [ "$list" $StringEQ "LIST" ]; then
	echo "-------------------------------------------------------"
	echo "List of instances, databases :"
  fi

  RC=4
  t=1
  while [ $t -le $dbi ]
  do
	#-----------------------------------------------------------------------
	# List database directory if requested
	#-----------------------------------------------------------------------
	if [ "$list" $StringEQ "LIST" ]; then
		echo "- Instance / Database (Type) : ${DBI[$t]} / ${DBN[$t]} (${DBT[$t]})"	
		RC=0
	fi

	#-----------------------------------------------------------------------
	# Activate instance environment if instance name found
	#-----------------------------------------------------------------------
 	if [ "$search" != "" -a "$search" $StringEQ "${DBI[$t]}" ]; then
		#echo ">>>${DBP[$t]}/db2profile"
		. ${DBP[$t]}/db2profile
		RC=$?
		echo "  => DB2 environment for instance ${DBI[$t]} set with RC=$RC."
		search=""
		lP_BANN=$PROG
		export lP_BANN
	fi

	#-----------------------------------------------------------------------
	# Activate instance environment if local database name found
	#-----------------------------------------------------------------------
 	if [ "$search" != "" -a "$search" $StringEQ "${DBN[$t]}" -a "${DBT[$t]}" $StringEQ "INDIRECT" ]; then
		#echo ">>>${DBP[$t]}/db2profile"
		. ${DBP[$t]}/db2profile
		RC=$?
		echo "  => DB2 environment for instance ${DBI[$t]} / ${DBN[$t]} set with RC=$RC."
		search=""
		lP_BANN=$PROG;
		export lP_BANN
	fi

	t=$t+1
  done

#-------------------------------------------------------------------------------
# Remove variables
#-------------------------------------------------------------------------------
  unset db2sw db2greg
  unset ilist list
  unset db dbi dbl
  unset r i ix p pi t search maxrc rc
  unset instname instpath
  unset regfile 
  unset DBI[*] DBN[*] DBP[*] DBT[*]
		
  echo "$PROG finished (RC=$RC)."

return $RC

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

#!/bin/ksh

################################################################################

# xmpSetInstance.sh - set DB2 environment

# ------------------------------------------------------------------------------

# Parameter: inst|db - instance or database name

# LIST - list databases

# System : AIX

# SunOS (Solaris)

# Comment : On servers with more than one DB2 instance, user probably needs to

# switch between instances. The original IBM korn shell script

# ". db2dprofile" does not support switching between instance, but

# concatenates instance settings.

# How to determine and check DB2 profile registry for installed

# DB2 instances: < V9.1: /tmp/profiles.reg

# = V9.1: /opt/IBM/db2/V9.1/profiles.reg

# = V9.7: /var/[opt/]db2/global.reg

# Since Version 9.1 DB2 provides the db2ls command which lists all

# installed products and their installation paths. Using the most

# current version of the db2greg command will list all installed

# instances.

# ------------------------------------------------------------------------------

# Autor G.Ruban /22.05.02 - erste Version V1.0

# Changes G.Ruban /09.07.02 - V1.2: equal operand differs on platforms

# G.Ruban /28.08.02 - V1.2.1: equal operand differs on platforms

# G.Ruban /22.10.02 - V1.2.2: unset variables

# G.Ruban /05.12.06 - V2.0: Some amendments necessary for DB2 V9.1

# G.Ruban /14.05.07 - V2.1: use /usr/local/bin if db2ls not in path

# G.Ruban /13.07.11 - V3.0: Upgrade to DB2 V9.7, profiles.reg not

# longer supported

# G.Ruban /09.09.11 - V3.1: Problem new with AIX 6.1 solved

################################################################################

#-------------------------------------------------------------------------------

# Init

#-------------------------------------------------------------------------------

PROG="xmpSetInstance.sh"

VER="3.1"

typeset -i dbi

typeset -i t

dbi=0

#-------------------------------------------------------------------------------

# Plaform-specific

#-------------------------------------------------------------------------------

case `uname` in

AIX)

StringEQ="="

;;

SunOS)

StringEQ="=="

;;

echo "$PROG: Unknown plaform, is not AIX or SunOS."

exit 8

;;

esac

#-------------------------------------------------------------------------------

# Check arguments

#-------------------------------------------------------------------------------

if [ $# -lt 1 -o "$1" $StringEQ "?" ] ; then

echo "Requested syntax: $PROG [inst|db] [LIST]"

echo "... where inst = name of local instance"

echo " db = name of local database"

echo " LIST = also lists databases"

echo "abends with RC=4"

exit 4

typeset -u search=$1

typeset -u list=$2

if [ $# -eq 1 -a "$search" $StringEQ "LIST" ]; then

search=""

typeset -u list=$1

#-------------------------------------------------------------------------------

# Determine db2ls command to inquire installed software

#-------------------------------------------------------------------------------

db2lspath=`which db2ls`

rc=$?

if [ $rc -ne 0 ] ; then

echo ">>> $PROG: DB2 Utility db2ls not found in current path."

db2lspath="/usr/local/bin/db2ls"

echo ">>> Execution path set to $db2lspath."

#-------------------------------------------------------------------------------

# Inquire latest installation path of DB2 (provides 1 item)

#-------------------------------------------------------------------------------

db2sw=`$db2lspath | grep db2 | awk '{print $1}' | tail -1`

rc=$?

if [ $rc -ne 0 ] ; then

echo "Neither DB2 V9.7 nor V9.1 found, continue searching older versions ..."

echo "abends with RC=16"

exit 16

if [ ! "$db2sw" ]; then

echo "$0: DB2 command db2ls found but no installed DB2 products found!"

echo "abends with RC=16"

exit 16

#-------------------------------------------------------------------------------

# For archive reasons only: Determination of profiles.reg and global.reg

# < V9.1: /tmp/profiles.reg

# = V9.1: /opt/IBM/db2/V9.1/profiles.reg

# = V9.7: /var/[opt/]db2/global.reg

# regfile=`find /var/db2/ -name 'global.reg' -type f -print;find /var/opt/db2/ -name 'global.reg' -type f -print;find /var/db2/ -name 'profiles.reg' -type f -print;find $db2ip -name 'profiles.reg' -type f -print`

#-------------------------------------------------------------------------------

# Inquire all instances from DB2 registry, list instance name and path only

#-------------------------------------------------------------------------------

db2greg=$db2sw"/bin/db2greg -dump"

#echo ">>> $db2greg"

ilist=`$db2greg | awk -F, '/I,DB2/ {print $4":"$5}'`

#echo ">>> $ilist"

#-------------------------------------------------------------------------------

# Provide information

#-------------------------------------------------------------------------------

echo $PROG " - V$VER"

echo "======================================================="

echo "Current User ................: "`id`

echo "Current Date/Time ...........: "`date +%Y-%m-%d-%H.%M.%S`

echo "System ......................: "`uname -n`

echo "Required Instance or Database: $search"

echo "List of instances and databases available at this server ..."

#-------------------------------------------------------------------------------

# Check for repeated invocation

#-------------------------------------------------------------------------------

if [ "$lP_BANN" $StringEQ "$PROG" ]; then

echo "-------------------------------------------------------"

echo "Warning, $PROG invoked repeatedly!"

echo "DB2 environment may not be set correctly!"

echo "Use env command to check PATH, LIBPATH etc."

echo "-------------------------------------------------------"

#-------------------------------------------------------------------------------

# Process all instances

#-------------------------------------------------------------------------------

maxrc=0

#echo ">>>$ilist<<<"

for i in $ilist

#---------------------------------------------------------------------------

# split instance name and instance path

#---------------------------------------------------------------------------

instname=${i%:*}

instpath=${i#*:}

#---------------------------------------------------------------------------

# Check existance of db2profile script (for execution in next step)

#---------------------------------------------------------------------------

if [ -x "$instpath/db2profile" ]; then

echo "- Instance / Home Directory : $instname $instpath"

dbi=$dbi+1

DBT[$dbi]="see below"

DBN[$dbi]="list of databases"

typeset -u DBI[$dbi]=$i

DBP[$dbi]=$p

#-----------------------------------------------------------

# List database directory at instance

#-----------------------------------------------------------

dbl=`ksh -c ". $instpath/db2profile; db2 list db directory|egrep '(alias|type)'|cut -f2 -d'='; exit"`

rc=$?

#-----------------------------------------------------------

# Store database names and types

#-----------------------------------------------------------

for db in $dbl

case $db in

Indirect) typeset -u DBT[$dbi]=$db;;

Remote) typeset -u DBT[$dbi]=$db;;

*) dbi=$dbi+1

typeset -u DBN[$dbi]=$db

typeset -u DBI[$dbi]=$instname

DBP[$dbi]=$instpath;;

esac

done

#-------------------------------------------------------------------------------

# Activate instance environment if instance name or database found

#-------------------------------------------------------------------------------

if [ "$list" $StringEQ "LIST" ]; then

echo "-------------------------------------------------------"

echo "List of instances, databases :"

RC=4

t=1

while [ $t -le $dbi ]

#-----------------------------------------------------------------------

# List database directory if requested

#-----------------------------------------------------------------------

if [ "$list" $StringEQ "LIST" ]; then

echo "- Instance / Database (Type) : ${DBI[$t]} / ${DBN[$t]} (${DBT[$t]})"

RC=0

#-----------------------------------------------------------------------

# Activate instance environment if instance name found

#-----------------------------------------------------------------------

if [ "$search" != "" -a "$search" $StringEQ "${DBI[$t]}" ]; then

#echo ">>>${DBP[$t]}/db2profile"

. ${DBP[$t]}/db2profile

RC=$?

echo " => DB2 environment for instance ${DBI[$t]} set with RC=$RC."

search=""

lP_BANN=$PROG

export lP_BANN

#-----------------------------------------------------------------------

# Activate instance environment if local database name found

#-----------------------------------------------------------------------

if [ "$search" != "" -a "$search" $StringEQ "${DBN[$t]}" -a "${DBT[$t]}" $StringEQ "INDIRECT" ]; then

#echo ">>>${DBP[$t]}/db2profile"

. ${DBP[$t]}/db2profile

RC=$?

echo " => DB2 environment for instance ${DBI[$t]} / ${DBN[$t]} set with RC=$RC."

search=""

lP_BANN=$PROG;

export lP_BANN

t=$t+1

done

#-------------------------------------------------------------------------------

# Remove variables

#-------------------------------------------------------------------------------

unset db2sw db2greg

unset ilist list

unset db dbi dbl

unset r i ix p pi t search maxrc rc

unset instname instpath

unset regfile

unset DBI[*] DBN[*] DBP[*] DBT[*]

echo "$PROG finished (RC=$RC)."

return $RC

REXX procedure to execute DSNACCOX DB2-supplied stored procedure (V9)

DSNACCOX, the successor of DSNACCOR, is a IBM DB2-supplied stored procedure to provide information to maintain DB2 objects. It includes proposals for creating image copyies (COPY), reorganization (REORG) and renewal of statistics (RUNSTATS).

This version covers DB2 for z/OS Version 9.

/*REXX****************************************************************/
 /* XMPACCOX ..: Retrieve recommendations to help you maintain        */
 /*              DB2 V9 z/OS databases using DSNACCOX stored procedure*/
 /*-------------------------------------------------------------------*/
 /* CALLED BY..: IKJEFT01   - TSO Batch oder Online                   */
 /* ARGUMENTS..: SSID       - Local/Home Subsystem                    */
 /*              CRITERIA   - Criteria for DSNACCOX's recommendations */
 /* CALLS......: DSNREXX  M - DB2/REXX Interface                      */
 /*              XMPABND  M - Abend U0001 (Non-ISPF)                  */
 /*-------------------------------------------------------------------*/
 /* COMMENTS...: Verify that SDSNLOAD ist part of LINKLIST or already */
 /*              allocated to TSO session. If not, you need to        */
 /*              dynamically allocate. (See STEPLIBX in code below!)  */
 /*-------------------------------------------------------------------*/
 /* FUNCTION...: Calls DSNACOXX stored procedure to determine objects */
 /*              which need maintenance (copy, runstats, reorg, all). */
 /*-------------------------------------------------------------------*/
 /* HISTORY....: 19.08.10/G.Ruban - V2R0 adapted from DSNACCOR->COX   */
 /*********************************************************************/
 x = MSG("OFF")

 /*********************************************************************/
 /* process arguments passed to procedure                             */
 /*********************************************************************/
 parse upper arg arglist
 ver = "2.0"
 Numeric Digits 12

 if pos("DEBUG",arglist)>0
 then do
      debug = "DEBUG"
      TRACE ?I
      SQLTRACE = "TRACE"
 end

 /*********************************************************************/
 /* Some environment information and initialzations                   */
 /*********************************************************************/
 PARSE SOURCE,
    source_host_env,
    source_call_type,
    source_uc_exec_name,
    source_hostdd,
    source_hostdsn,
    source_nf_exec_name,
    source_initial_cmd_env,
    source_asid_name

 jobnamestep   = Get_Jobname()
 sysvar_sysuid = SYSVAR("SYSUID")
 sysvar_sysenv = SYSVAR("SYSENV")

 /*********************************************************************/
 /* Header                                                            */
 /*********************************************************************/
 "CLRSCRN"
 say copies("#",79)
 say "# XMPACCOX V"ver" - Recommendations to maintain DB2 objects ",
     " "date("O")" "time()" #";
 say copies("#",79)
 say ""

 /*********************************************************************/
 /* Cechk required environment                                        */
 /*********************************************************************/
 if (source_host_env ^= "TSO") then do
   Say "Wrong host environment" source_host_env
   signal error
 end

 parse var arglist ssid CRITERIA
 if debug = "DEBUG"
 then do
      i = pos("DEBUG",ssid)
      if i>0 then ssid = delstr(ssid,i,5)
      i = wordpos("DEBUG",CIRTERIA)
      if i>9 then CRITERIA = delword(CRITERIA,i,1)
 end

 if ssid = "" ! ssid = "DEBUG"
 then do
      say "Executing without arguments,",
          "setting default DB2 subsystem id and criteria."
      ssid     = "DB2T"
      CRITERIA = "DBNAME LIKE 'DSN%'",          /* WHERE CLAUSE 4096 */
                 "AND NAME LIKE '%'"
 end
 CRITERIA_IND = 0

 say "XMPACCOX ...: Following values have been set for DSNACCOX"
 say "DB2 SSID ...:" ssid
 say "CHKLVL .....: Use the chklvl parameter to cause dsnaccox to"
 say "              check for related table spaces when processing"
 say "              index spaces, and delete recommendation for"
 say "              indexspaces when an action (such as reorg) on the"
 say "              table space will also cause the action to be done"
 say "              on the index space. (CHKLVL=64) "
 say "CRITERIA ...:" CRITERIA
 say "              Use the criteria parameter to cause DSNACCOX to "
 say "              make recommendations only for objects in databases"
 say "              you want to."
 say "Other parms : Following parameter values have been set, which are"
 say "              lower than the defaults:"
 say "              CRUPDATEDPAGESPCT  4          RRIAPPENDINSERTPCT 5"
 say "              CRCHANGESPCT       2          SRTINSDELUPDPCT    5"
 say "              RRTINSERTPCT       2          SRIINSDELPCT       5"
 say "              RRTUNCLUSTINSPCT   5          EXTENTLIMIT        3"
 say "              RRTDISORGLOBPCT    5"
 say "              Excluded the checking for these criteria by set the"
 say "              following values to a negative value:"
 say "              RRTMASSDELLIMIT    -1         RRIMASSDELLIMIT    -1"
 say ""

 /*********************************************************************/
 /* If DB2 Load Library is not part of z/OS LINKLIST:                 */
 /* Allocate SDSNLOAD using CA TSOPLUS (or any other which adds the   */
 /* DB2 load libray dynamically to the STEPLIB).                      */
 /*********************************************************************/
  address TSO
  "FREE  FI(STEPLIBX) DA('SAMPLE."ssid".SDSNLOAD')"
  "ALLOC FI(STEPLIBX) DA('SAMPLE."ssid".SDSNLOAD') SHR REUSE"
  if rc <> 0
  then do
       say "DB2 SDSNLOAD library for SSID="ssid" is not available!"
       say "Check z/OS LINKLIST or allocate to STEPLIB in advance!"
       signal error
  end

  ADDRESS TSO "SUBCOM DSNREXX"
  if rc <> 0
  then do
       cc = RXSUBCOM("ADD","DSNREXX","DSNREXX")
       if cc
       then do
            say "DB2 SDSNLOAD library not available!"
            say "Check z/OS LINKLIST or allocate to STEPLIB in advance!"
            signal error
       end
  end

 /*********************************************************************/
 /* Setup utility name and utility statement variables:               */
 /*********************************************************************/
 QUERYTYPE          = "ALL";      QUERYTYPE_IND             = 0 ;
 OBJECTTYPE         = "ALL";      OBJECTTYPE_IND            = 0 ;
 ICTYPE             = "B";        ICTYPE_IND                = 0 ;
 CATLGSCHEMA        = "SYSIBM";   CATLGSCHEMA_IND           = 0 ;
 LOCALSCHEMA        = "DSNACC";   LOCALSCHEMA_IND           = 0 ;
 CHKLVL             = 64;         CHKLVL_IND                = 0 ;
 UNUSED             = "";         UNUSED_IND                = -1;
 CRUPDATEDPAGESPCT  = 0 ;         CRUPDATEDPAGESPCT_IND     = -1;
 CRUPDATEDPAGESABS  = 0 ;         CRUPDATEDPAGESABS_IND     = -1;
 CRCHANGESPCT       = 0 ;         CRCHANGESPCT_IND          = -1;
 CRDAYSNCLASTCOPY   = 0 ;         CRDAYSNCLASTCOPY_IND      = -1;
 ICRUPDATEDPAGESPCT = 0 ;         ICRUPDATEDPAGESPCT_IND    = -1;
 ICRUPDATEDPAGESABS = 0 ;         ICRUPDATEDPAGESABS_IND    = -1;
 ICRCHANGESPCT      = 0 ;         ICRCHANGESPCT_IND         = -1;
 CRINDEXSIZE        = 0 ;         CRINDEXSIZE_IND           = -1;
 RRTINSERTSPCT      = 0 ;         RRTINSERTSPCT_IND         = -1;
 RRTINSERTSABS      = 0 ;         RRTINSERTSABS_IND         = -1;
 RRTDELETESPCT      = 0 ;         RRTDELETESPCT_IND         = -1;
 RRTDELETESABS      = 0 ;         RRTDELETESABS_IND         = -1;
 RRTUNCLUSTINSPCT   = 0 ;         RRTUNCLUSTINSPCT_IND      = -1;
 RRTDISORGLOBPCT    = 0 ;         RRTDISORGLOBPCT_IND       = -1;
 RRTDATASPACERAT    = 0 ;         RRTDATASPACERAT_IND       = -1;
 RRTMASSDELLIMIT    = 0 ;         RRTMASSDELLIMIT_IND       = -1;
 RRTINDREFLIMIT     = 0 ;         RRTINDREFLIMIT_IND        = -1;
 RRIINSERTSPCT      = 0 ;         RRIINSERTSPCT_IND         = -1;
 RRIINSERTSABS      = 0 ;         RRIINSERTSABS_IND         = -1;
 RRIDELETESPCT      = 0 ;         RRIDELETESPCT_IND         = -1;
 RRIDELETESABS      = 0 ;         RRIDELETESABS_IND         = -1;
 RRIAPPENDINSERTPCT = 0 ;         RRIAPPENDINSERTPCT_IND    = -1;
 RRIPSEUDODELETEPCT = 0 ;         RRIPSEUDODELETEPCT_IND    = -1;
 RRIMASSDELLIMIT    = 0 ;         RRIMASSDELLIMIT_IND       = -1;
 RRILEAFLIMIT       = 0 ;         RRILEAFLIMIT_IND          = -1;
 RRINUMLEVELSLIMIT  = 0 ;         RRINUMLEVELSLIMIT_IND     = -1;
 SRTINSDELUPDPCT    = 0 ;         SRTINSDELUPDPCT_IND       = -1;
 SRTINSDELUPDABS    = 0 ;         SRTINSDELUPDABS_IND       = -1;
 SRTMASSDELLIMIT    = 0 ;         SRTMASSDELLIMIT_IND       = -1;
 SRIINSDELUPDPCT    = 0 ;         SRIINSDELUPDPCT_IND       = -1;
 SRIINSDELUPDABS    = 0 ;         SRIINSDELUPDABS_IND       = -1;
 SRIMASSDELLIMIT    = 0 ;         SRIMASSDELLIMIT_IND       = -1;
 EXTENTLIMIT        = 0 ;         EXTENTLIMIT_IND           = -1;
 LASTSTATEMENT      = 0 ;         LASTSTATEMENT_IND         = -1;
 RETURNCODE         = 0 ;         RETURNCODE_IND            = -1;
 ERRORMSG           = 0 ;         ERRORMSG_IND              = -1;
 IFCARETCODE        = 0 ;         IFCARETCODE_IND           = -1;
 IFCARESCODE        = 0 ;         IFCARESCODE_IND           = -1;
 XSBYTES            = 0 ;         XSBYTES_IND               = -1;

 /*********************************************************************/
 /* Set the indicator variables to 0 for non-null input parameters    */
 /* (parameters for which you do not want DSNACCOX to use default     */
 /* values) and for output parameters.                                */
 /*********************************************************************/
 CRUPDATEDPAGESPCT  = 4 ;         CRUPDATEDPAGESPCT_IND     = 0 ;
 CRCHANGESPCT       = 2 ;         CRCHANGESPCT_IND          = 0 ;
 RRTINSDELUPDPCT    = 2 ;         RRTINSDELUPDPCT_IND       = 0 ;
 RRTUNCLUSTINSPCT   = 5 ;         RRTUNCLUSTINSPCT_IND      = 0 ;
 RRTDISORGLOBPCT    = 5 ;         RRTDISORGLOBPCT_IND       = 0 ;
 RRIAPPENDINSERTPCT = 5 ;         RRIAPPENDINSERTPCT_IND    = 0 ;
 SRTINSDELUPDPCT    = 5 ;         SRTINSDELUPDPCT_IND       = 0 ;
 SRIINSDELPCT       = 5 ;         SRIINSDELPCT_IND          = 0 ;
 EXTENTLIMIT        = 3 ;         EXTENTLIMIT_IND           = 0 ;
 RRTMASSDELLIMIT    = 0 ;         RRTMASSDELLIMIT_IND       = -1 ;
 RRIMASSDELLIMIT    = 0 ;         RRIMASSDELLIMIT_IND       = -1 ;

 LASTSTATEMENT      = COPIES("",8012);
                                  LASTSTATEMENT_IND         = 0;
 RETURNCODE         = 0;          RETURNCODE_IND            = 0;
 ERRORMSG           = COPIES("",1331);
                                  ERRORMSG_IND              = 0;
 IFCARETCODE        = 0;          IFCARETCODE_IND           = 0;
 IFCARESCODE        = 0;          IFCARESCODE_IND           = 0;
 XSBYTES            = 0;          XSBYTES_IND               = 0;

 /*********************************************************************/
 /* call DB2 stored procedure                                         */
 /*********************************************************************/
 sqlstmt = "CALL SYSPROC.DSNACCOX",
              "(:QUERYTYPE          :QUERYTYPE_IND, " ,
              " :OBJECTTYPE         :OBJECTTYPE_IND, " ,
              " :ICTYPE             :ICTYPE_IND, " ,
              " :CATLGSCHEMA        :CATLGSCHEMA_IND, " ,
              " :LOCALSCHEMA        :LOCALSCHEMA_IND, " ,
              " :CHKLVL             :CHKLVL_IND, " ,
              " :CRITERIA           :CRITERIA_IND, " ,
              " :UNUSED             :UNUSED_IND, " ,
              " :CRUPDATEDPAGESPCT  :CRUPDATEDPAGESPCT_IND, " ,
              " :CRUPDATEDPAGESABS  :CRUPDATEDPAGESABS_IND, " ,
              " :CRCHANGESPCT       :CRCHANGESPCT_IND, " ,
              " :CRDAYSNCLASTCOPY   :CRDAYSNCLASTCOPY_IND, " ,
              " :ICRUPDATEDPAGESPCT :ICRUPDATEDPAGESPCT_IND, " ,
              " :ICRUPDATEDPAGESABS :ICRUPDATEDPAGESABS_IND, " ,
              " :ICRCHANGESPCT      :ICRCHANGESPCT_IND, " ,
              " :CRINDEXSIZE        :CRINDEXSIZE_IND, " ,
              " :RRTINSERTSPCT      :RRTINSERTSPCT_IND, " ,
              " :RRTINSERTSABS      :RRTINSERTSABS_IND, " ,
              " :RRTDELETESPCT      :RRTDELETESPCT_IND, " ,
              " :RRTDELETESABS      :RRTDELETESABS_IND, " ,
              " :RRTUNCLUSTINSPCT   :RRTUNCLUSTINSPCT_IND, " ,
              " :RRTDISORGLOBPCT    :RRTDISORGLOBPCT_IND, " ,
              " :RRTDATASPACERAT    :RRTDATASPACERAT_IND, " ,
              " :RRTMASSDELLIMIT    :RRTMASSDELLIMIT_IND, " ,
              " :RRTINDREFLIMIT     :RRTINDREFLIMIT_IND, " ,
              " :RRIINSERTSPCT      :RRIINSERTSPCT_IND, " ,
              " :RRIINSERTSABS      :RRIINSERTSABS_IND, " ,
              " :RRIDELETESPCT      :RRIDELETESPCT_IND, " ,
              " :RRIDELETESABS      :RRIDELETESABS_IND, " ,
              " :RRIAPPENDINSERTPCT :RRIAPPENDINSERTPCT_IND, " ,
              " :RRIPSEUDODELETEPCT :RRIPSEUDODELETEPCT_IND, " ,
              " :RRIMASSDELLIMIT    :RRIMASSDELLIMIT_IND, " ,
              " :RRILEAFLIMIT       :RRILEAFLIMIT_IND, " ,
              " :RRINUMLEVELSLIMIT  :RRINUMLEVELSLIMIT_IND, " ,
              " :SRTINSDELUPDPCT    :SRTINSDELUPDPCT_IND, " ,
              " :SRTINSDELUPDABS    :SRTINSDELUPDABS_IND, " ,
              " :SRTMASSDELLIMIT    :SRTMASSDELLIMIT_IND, " ,
              " :SRIINSDELUPDPCT    :SRIINSDELUPDPCT_IND, " ,
              " :SRIINSDELUPDABS    :SRIINSDELUPDABS_IND, " ,
              " :SRIMASSDELLIMIT    :SRIMASSDELLIMIT_IND, " ,
              " :EXTENTLIMIT        :EXTENTLIMIT_IND, " ,
              " :LASTSTATEMENT      :LASTSTATEMENT_IND, " ,
              " :RETURNCODE         :RETURNCODE_IND, " ,
              " :ERRORMSG           :ERRORMSG_IND, " ,
              " :IFCARETCODE        :IFCARETCODE_IND, " ,
              " :IFCARESCODE        :IFCARESCODE_IND, " ,
              " :XSBYTES            :XSBYTES_IND)";

 ADDRESS DSNREXX "CONNECT" ssid
 call Code 0

 address DSNREXX "EXECSQL" sqlstmt
 call Code 0 466                        /* +466 = more rows to fetch */

 if returncode = 0
 then do
   /* say copies("=",79) */
   /* say "Approximate "space(delword(errormsg,1,1)),              */
   /*     "of the total number of objects in the subsystem that", */
   /*     "have information in the real-time statistics tables." */
   /* say copies("=",79) */
   /* say ""             */
      say "Recommendations: You should watch ..."
      say copies("-",79)
 end
 else do
      if returncode =  4
      then Say "DSNACCOX completed, but with incompatible parms."
      if returncode =  8
      then say "DSNACCOX terminated with errors."
      if returncode = 12
      then say "DSNACCOX terminated with severe errors."
      if returncode = 14
      then say "You need to create the RTS tables or grant access."
      if returncode = 16
      then say "You need to create a TEMP database and tablespaces."
      say "Error Message......: "errormsg
      say "IFCA Return Code...: "IFCARetCode
      say "IFCA Reseas Code...: "IFCAResCode
      say "Last Statement.....: "laststatement
      signal error
 end
 /*********************************************************************/
 /* Combine two locators with the stored procedure                    */
 /*********************************************************************/
 LOC1 = d2x(0)
 LOC2 = d2x(0)
 sqlstmt = "ASSOCIATE LOCATORS (:LOC1, :LOC2)",
           "WITH PROCEDURE SYSPROC.DSNACCOX"
 address DSNREXX "EXECSQL" sqlstmt;
 call code 0

 /*********************************************************************/
 /* Allocate and process cursor-driven                                */
 /*********************************************************************/
 sqlstmt = "ALLOCATE C101 CURSOR FOR RESULT SET :LOC1"
 address DSNREXX "EXECSQL" sqlstmt;
 do while sqlcode = 0
   call code 0
   sqlstmt = "FETCH C101 INTO :RS_SEQ, :RS_DATA"
   Address DSNREXX "EXECSQL" sqlstmt;
   /*say RS_SEQ RS_DATA*/
 end
 call code 0 100

 sqlstmt = "CLOSE C101"
 ADDRESS DSNREXX "EXECSQL "sqlstmt;
 call code 0 100

 /*********************************************************************/
 /* Allocate and process cursor-driven                                */
 /*********************************************************************/
 sqlstmt = "ALLOCATE C102 CURSOR FOR RESULT SET :LOC2"
 address DSNREXX "EXECSQL" sqlstmt;
 call Code 0

 /*********************************************************************/
 /* Fetch all messages via the cursor                                 */
 /*********************************************************************/
 sqlstmt = "FETCH C102 INTO ",
                  ":DBNAME             :DBNAME_IND           ,",
                  ":NAME               :NAME_IND             ,",
                  ":PARTITION          :PARTITION_IND        ,",
                  ":INSTANCE           :INSTANCE_IND         ,",
                  ":CLONE              :CLONE_IND            ,",
                  ":OBJECTTYPE         :OBJECTTYPE_IND       ,",
                  ":INDEXSPACE         :INDEXSPACE_IND       ,",
                  ":CREATOR            :CREATOR_IND          ,",
                  ":OBJECTSTATUS       :OBJECTSTATUS_IND     ,",
                  ":IMAGECOPY          :IMAGECOPY_IND        ,",
                  ":RUNSTATS           :RUNSTATS_IND         ,",
                  ":EXTENTS            :EXTENTS_IND          ,",
                  ":REORG              :REORG_IND            ,",
                  ":INEXCEPTTABLE      :INEXCEPTTABLE_IND    ,",
                  ":ASSOCIATEDTS       :ASSOCIATEDTS_IND     ,",
                  ":COPYLASTTIME       :COPYLASTTIME_IND     ,",
                  ":LOADRLASTTIME      :LOADRLASTTIME_IND    ,",
                  ":REBUILDLASTTIME    :REBUILDLASTTIME_IND  ,",
                  ":CRUPDPGSPCT        :CRUPDPGSPCT_IND      ,",
                  ":CRUPDPGSABS        :CRUPDPGSABS_IND      ,",
                  ":CRCPYCHGPCT        :CRCPYCHGPCT_IND      ,",
                  ":CRDAYSCELSTCPY     :CRDAYSCELSTCPY_IND   ,",
                  ":CRINDEXSIZE        :CRINDEXSIZE_IND      ,",
                  ":REORGLASTTIME      :REORGLASTTIME_IND    ,",
                  ":RRTINSERTSPCT      :RRTINSERTSPCT_IND    ,",
                  ":RRTINSERTSABS      :RRTINSERTSABS_IND    ,",
                  ":RRTDELETESPCT      :RRTDELETESPCT_IND    ,",
                  ":RRTDELETESABS      :RRTDELETESABS_IND    ,",
                  ":RRTUNCINSPCT       :RRTUNCINSPCT_IND     ,",
                  ":RRTDISORGLOBPCT    :RRTDISORGLOBPCT_IND  ,",
                  ":RRTDATASPACERAT    :RRTDATASPACERAT_IND  ,",
                  ":RRTMASSDELETE      :RRTMASSDELETE_IND    ,",
                  ":RRTINDREF          :RRTINDREF_IND        ,",
                  ":RRIINSERTPCT       :RRIINSERTPCT_IND     ,",
                  ":RRIINSERTABS       :RRIINSERTABS_IND     ,",
                  ":RRIDELETEPCT       :RRIDELETEPCT_IND     ,",
                  ":RRIDELETABS        :RRIDELETABS_IND      ,",
                  ":RRIAPPINSPCT       :RRIAPPINSPCT_IND     ,",
                  ":RRIPSDDELPCT       :RRIPSDDELPCT_IND     ,",
                  ":RRIMASSDELETE      :RRIMASSDELETE_IND    ,",
                  ":RRILEAF            :RRILEAF_IND          ,",
                  ":RRINUMLEVELS       :RRINUMLEVELS_IND     ,",
                  ":STATSLASTTIME      :STATSLASTTIME_IND    ,",
                  ":SRTINSDELUPDPCT    :SRTINSDELUPDPCT_IND  ,",
                  ":SRTINSDELUPDABS    :SRTINSDELUPDABS_IND  ,",
                  ":SRTMASSDELETE      :SRTMASSDELETE_IND    ,",
                  ":SRIINSDELPCT       :SRIINSDELPCT_IND     ,",
                  ":SRIINSDELABS       :SRIINSDELABS_IND     ,",
                  ":SRIMASSDELETE      :SRIMASSDELETE_IND    ,",
                  ":TOTALEXTENTS       :TOTALEXTENTS_IND     ";
 do while sqlcode = 0
    if OBJECTTYPE <> "ALL"
    then do
         Say "DATABASE: "DBNAME" SPACENAME: "NAME "("OBJECTTYPE")"
         if IMAGECOPY <> "NO"
         then do
              if COPYLASTTIME_IND = -1 then COPYLASTTIME = "NEVER"
              say "- Imagecopy ...: "IMAGECOPY", last done "COPYLASTTIME
              call Line(CRUPDPGSPCT "% updated pages")
              call Line(CRCPYCHGPCT "% rows INS/DEL/UPD")
              call Line(CRDAYSCELSTCPY "day(s) since last COPY")
         end
         if RUNSTATS <> "NO"
         then do
              if STATSLASTTIME_IND = -1 then STATSLASTTIME = "NEVER"
              say "- Runstats ....: "RUNSTATS", last done "STATSLASTTIME
              call Line(SRTMASSDELETE "mass deleted")
              call Line(SRTINSDELUPDPCT "% rows INS/DEL/UPD")
         end
         if REORG <> "NO"
         then do
              if REORGLASTTIME_IND = -1 then REORGLASTTIME = "NEVER"
              say "- Reorg .......: "REORG", last done "REORGLASTTIME
              call Line(RRTMASSDELETE "mass deleted")
              call Line(RRTINSERTSPCT "% rows INS/DEL/UPD")
              call line(RRTUNCINSPCT "% rows unclustered INSERT's")
              call line(RRTINDREF "% overflowed records")
         end
         if EXTENTS <> "NO"
         then do
              say "- Extents .....: "EXTENTS", "TOTALEXTENTS" extent(s)"
         end
    end
    Address DSNREXX "EXECSQL" SQLSTMT
 end
 call code 0 100

 sqlstmt = "CLOSE C102"
 address DSNREXX "EXECSQL "sqlstmt;
 call code 0 100

 say "*** end ***"

/*********************************************************************/
/* Disconnect vom Subsystem                                          */
/*********************************************************************/
Disconnect:
    ADDRESS DSNREXX "DISCONNECT"
    cc = RXSUBCOM("DELETE","DSNREXX","DSNREXX")
return
/*********************************************************************/
/* Error processing and termination                                  */
/*********************************************************************/
SYNTAX:
      say "XMPACCOX: Syntax error."
      say "          [TSO] XMPACCOX ssid options"
      say "          ssid .......: DB2 Subsystem ID"
      say "          criteria ...: criteria for DSNACCOX"
ERROR:
      say ""
      say "Current enviroment:" jobnamestep source_host_env,
          source_call_type source_uc_exec_name source_hostdd,
          source_hostdsn source_nf_exec_name source_initial_cmd_env
      If Sysvar(SYSISPF) = "NOT ACTIVE"
      then do
           say "REXX procedure XMPACCOX ends with U0001."
           address ATTACH "XMPABND"
           exit 0
      end
      say "REXX procedure XMPACCOX ends with RC=20."
exit 20

/*********************************************************************/
/* Check SQLCODE's allowed                                           */
/*********************************************************************/
Code:
   arg codes
   allowed = "NO"
   do ci=1 to words(codes)
      if sqlcode = word(codes,ci)
      then allowed = "YES"
   end
   if allowed = "NO"
   then do
        say "CONNECT: SQL Error with SQLCODE="sqlcode
        call TIAR sqlcode
        say "         "sqlstmt
        call Disconnect
        signal error
   end
return

/*********************************************************************/
/* process line output, hide if value contains no information        */
/*********************************************************************/
Line: arg lval ltext
      lval = trunc(lval)
      if lval > 0
      then say right(lval,20)" "ltext
return

/*********************************************************************/
/* Retrieve some internal information from z/OS control blocks       */
/*********************************************************************/
GET_JOBNAME: Procedure
   /* all fields are part of general use prog'ing interface */
   PSATOLD  = '21C'X
   TCB      = C2D(STORAGE(C2X(PSATOLD),4))
   TIOT     = C2D(STORAGE(D2X(TCB+12),4))
   TIOCNJOB = STRIP(STORAGE(D2X(TIOT),8))
   TIOCSTEP = STRIP(STORAGE(D2X(TIOT+8),8))
Return TIOCNJOB TIOCSTEP

/*********************************************************************/
/* Display SQL Commmunication area using DSNTIAR                     */
/*********************************************************************/
TIAR: arg sqlcode
      sqlc = d2x(sqlcode,8)
      sqlc = x2c(sqlc)

      sqlca = 'sqlca   '
      sqlca = sqlca !!  x2c(00000088)
      sqlca = sqlca !!  sqlc
      sqlca = sqlca !!  x2c(0000)
      sqlca = sqlca !!  copies(' ',78)
      sqlca = sqlca !!  copies(x2c(00),24)
      sqlca = sqlca !!  copies(' ',16)
      tiar_msg = x2c(0190)copies(' ',400)
      text_len = x2c(00000050) ;
      /***************************************************************/
      /* Extract Message from Return Area                            */
      /***************************************************************/
      ADDRESS ATTCHPGM 'DSNTIAR SQLCA TIAR_MSG TEXT_LEN'
      say substr(tiar_msg,4,400);
      /***************************************************************/
      /* Extract Message from Return Area                            */
      /***************************************************************/
      len = c2d(substr(rtrnarea,5,2))
      /***************************************************************/
      /* Extract Message from Return Area                            */
      /***************************************************************/
      say "          SQLCODE="sqlcode
      say "          SQLERRMC="sqlerrmc
      say "          SQLWARN5="sqlwarn.5
      say "          SQLERRP ="sqlerrp
      say "          SQLSTATE="sqlstate
return

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

474

475

476

477

478

479

480

481

482

483

484

485

486

487

488

489

490

491

492

493

494

495

496

497

498

499

500

501

502

503

504

505

506

507

508

509

510

511

512

513

514

515

516

517

518

519

520

521

522

523

524

525

526

527

528

529

530

531

532

533

534

535

536

537

/*REXX****************************************************************/

/* XMPACCOX ..: Retrieve recommendations to help you maintain */

/* DB2 V9 z/OS databases using DSNACCOX stored procedure*/

/*-------------------------------------------------------------------*/

/* CALLED BY..: IKJEFT01 - TSO Batch oder Online */

/* ARGUMENTS..: SSID - Local/Home Subsystem */

/* CRITERIA - Criteria for DSNACCOX's recommendations */

/* CALLS......: DSNREXX M - DB2/REXX Interface */

/* XMPABND M - Abend U0001 (Non-ISPF) */

/*-------------------------------------------------------------------*/

/* COMMENTS...: Verify that SDSNLOAD ist part of LINKLIST or already */

/* allocated to TSO session. If not, you need to */

/* dynamically allocate. (See STEPLIBX in code below!) */

/*-------------------------------------------------------------------*/

/* FUNCTION...: Calls DSNACOXX stored procedure to determine objects */

/* which need maintenance (copy, runstats, reorg, all). */

/*-------------------------------------------------------------------*/

/* HISTORY....: 19.08.10/G.Ruban - V2R0 adapted from DSNACCOR->COX */

/*********************************************************************/

x = MSG("OFF")

/*********************************************************************/

/* process arguments passed to procedure */

/*********************************************************************/

parse upper arg arglist

ver = "2.0"

Numeric Digits 12

if pos("DEBUG",arglist)>0

then do

debug = "DEBUG"

TRACE ?I

SQLTRACE = "TRACE"

end

/*********************************************************************/

/* Some environment information and initialzations */

/*********************************************************************/

PARSE SOURCE,

source_host_env,

source_call_type,

source_uc_exec_name,

source_hostdd,

source_hostdsn,

source_nf_exec_name,

source_initial_cmd_env,

source_asid_name

jobnamestep = Get_Jobname()

sysvar_sysuid = SYSVAR("SYSUID")

sysvar_sysenv = SYSVAR("SYSENV")

/*********************************************************************/

/* Header */

/*********************************************************************/

"CLRSCRN"

say copies("#",79)

say "# XMPACCOX V"ver" - Recommendations to maintain DB2 objects ",

" "date("O")" "time()" #";

say copies("#",79)

say ""

/*********************************************************************/

/* Cechk required environment */

/*********************************************************************/

if (source_host_env ^= "TSO") then do

Say "Wrong host environment" source_host_env

signal error

end

parse var arglist ssid CRITERIA

if debug = "DEBUG"

then do

i = pos("DEBUG",ssid)

if i>0 then ssid = delstr(ssid,i,5)

i = wordpos("DEBUG",CIRTERIA)

if i>9 then CRITERIA = delword(CRITERIA,i,1)

end

if ssid = "" ! ssid = "DEBUG"

then do

say "Executing without arguments,",

"setting default DB2 subsystem id and criteria."

ssid = "DB2T"

CRITERIA = "DBNAME LIKE 'DSN%'", /* WHERE CLAUSE 4096 */

"AND NAME LIKE '%'"

end

CRITERIA_IND = 0

say "XMPACCOX ...: Following values have been set for DSNACCOX"

say "DB2 SSID ...:" ssid

say "CHKLVL .....: Use the chklvl parameter to cause dsnaccox to"

say " check for related table spaces when processing"

say " index spaces, and delete recommendation for"

say " indexspaces when an action (such as reorg) on the"

say " table space will also cause the action to be done"

say " on the index space. (CHKLVL=64) "

say "CRITERIA ...:" CRITERIA

say " Use the criteria parameter to cause DSNACCOX to "

say " make recommendations only for objects in databases"

say " you want to."

say "Other parms : Following parameter values have been set, which are"

say " lower than the defaults:"

say " CRUPDATEDPAGESPCT 4 RRIAPPENDINSERTPCT 5"

say " CRCHANGESPCT 2 SRTINSDELUPDPCT 5"

say " RRTINSERTPCT 2 SRIINSDELPCT 5"

say " RRTUNCLUSTINSPCT 5 EXTENTLIMIT 3"

say " RRTDISORGLOBPCT 5"

say " Excluded the checking for these criteria by set the"

say " following values to a negative value:"

say " RRTMASSDELLIMIT -1 RRIMASSDELLIMIT -1"

say ""

/*********************************************************************/

/* If DB2 Load Library is not part of z/OS LINKLIST: */

/* Allocate SDSNLOAD using CA TSOPLUS (or any other which adds the */

/* DB2 load libray dynamically to the STEPLIB). */

/*********************************************************************/

address TSO

"FREE FI(STEPLIBX) DA('SAMPLE."ssid".SDSNLOAD')"

"ALLOC FI(STEPLIBX) DA('SAMPLE."ssid".SDSNLOAD') SHR REUSE"

if rc <> 0

then do

say "DB2 SDSNLOAD library for SSID="ssid" is not available!"

say "Check z/OS LINKLIST or allocate to STEPLIB in advance!"

signal error

end

ADDRESS TSO "SUBCOM DSNREXX"

if rc <> 0

then do

cc = RXSUBCOM("ADD","DSNREXX","DSNREXX")

if cc

then do

say "DB2 SDSNLOAD library not available!"

say "Check z/OS LINKLIST or allocate to STEPLIB in advance!"

signal error

end

/*********************************************************************/

/* Setup utility name and utility statement variables: */

/*********************************************************************/

QUERYTYPE = "ALL"; QUERYTYPE_IND = 0 ;

OBJECTTYPE = "ALL"; OBJECTTYPE_IND = 0 ;

ICTYPE = "B"; ICTYPE_IND = 0 ;

CATLGSCHEMA = "SYSIBM"; CATLGSCHEMA_IND = 0 ;

LOCALSCHEMA = "DSNACC"; LOCALSCHEMA_IND = 0 ;

CHKLVL = 64; CHKLVL_IND = 0 ;

UNUSED = ""; UNUSED_IND = -1;

CRUPDATEDPAGESPCT = 0 ; CRUPDATEDPAGESPCT_IND = -1;

CRUPDATEDPAGESABS = 0 ; CRUPDATEDPAGESABS_IND = -1;

CRCHANGESPCT = 0 ; CRCHANGESPCT_IND = -1;

CRDAYSNCLASTCOPY = 0 ; CRDAYSNCLASTCOPY_IND = -1;

ICRUPDATEDPAGESPCT = 0 ; ICRUPDATEDPAGESPCT_IND = -1;

ICRUPDATEDPAGESABS = 0 ; ICRUPDATEDPAGESABS_IND = -1;

ICRCHANGESPCT = 0 ; ICRCHANGESPCT_IND = -1;

CRINDEXSIZE = 0 ; CRINDEXSIZE_IND = -1;

RRTINSERTSPCT = 0 ; RRTINSERTSPCT_IND = -1;

RRTINSERTSABS = 0 ; RRTINSERTSABS_IND = -1;

RRTDELETESPCT = 0 ; RRTDELETESPCT_IND = -1;

RRTDELETESABS = 0 ; RRTDELETESABS_IND = -1;

RRTUNCLUSTINSPCT = 0 ; RRTUNCLUSTINSPCT_IND = -1;

RRTDISORGLOBPCT = 0 ; RRTDISORGLOBPCT_IND = -1;

RRTDATASPACERAT = 0 ; RRTDATASPACERAT_IND = -1;

RRTMASSDELLIMIT = 0 ; RRTMASSDELLIMIT_IND = -1;

RRTINDREFLIMIT = 0 ; RRTINDREFLIMIT_IND = -1;

RRIINSERTSPCT = 0 ; RRIINSERTSPCT_IND = -1;

RRIINSERTSABS = 0 ; RRIINSERTSABS_IND = -1;

RRIDELETESPCT = 0 ; RRIDELETESPCT_IND = -1;

RRIDELETESABS = 0 ; RRIDELETESABS_IND = -1;

RRIAPPENDINSERTPCT = 0 ; RRIAPPENDINSERTPCT_IND = -1;

RRIPSEUDODELETEPCT = 0 ; RRIPSEUDODELETEPCT_IND = -1;

RRIMASSDELLIMIT = 0 ; RRIMASSDELLIMIT_IND = -1;

RRILEAFLIMIT = 0 ; RRILEAFLIMIT_IND = -1;

RRINUMLEVELSLIMIT = 0 ; RRINUMLEVELSLIMIT_IND = -1;

SRTINSDELUPDPCT = 0 ; SRTINSDELUPDPCT_IND = -1;

SRTINSDELUPDABS = 0 ; SRTINSDELUPDABS_IND = -1;

SRTMASSDELLIMIT = 0 ; SRTMASSDELLIMIT_IND = -1;

SRIINSDELUPDPCT = 0 ; SRIINSDELUPDPCT_IND = -1;

SRIINSDELUPDABS = 0 ; SRIINSDELUPDABS_IND = -1;

SRIMASSDELLIMIT = 0 ; SRIMASSDELLIMIT_IND = -1;

EXTENTLIMIT = 0 ; EXTENTLIMIT_IND = -1;

LASTSTATEMENT = 0 ; LASTSTATEMENT_IND = -1;

RETURNCODE = 0 ; RETURNCODE_IND = -1;

ERRORMSG = 0 ; ERRORMSG_IND = -1;

IFCARETCODE = 0 ; IFCARETCODE_IND = -1;

IFCARESCODE = 0 ; IFCARESCODE_IND = -1;

XSBYTES = 0 ; XSBYTES_IND = -1;

/*********************************************************************/

/* Set the indicator variables to 0 for non-null input parameters */

/* (parameters for which you do not want DSNACCOX to use default */

/* values) and for output parameters. */

/*********************************************************************/

CRUPDATEDPAGESPCT = 4 ; CRUPDATEDPAGESPCT_IND = 0 ;

CRCHANGESPCT = 2 ; CRCHANGESPCT_IND = 0 ;

RRTINSDELUPDPCT = 2 ; RRTINSDELUPDPCT_IND = 0 ;

RRTUNCLUSTINSPCT = 5 ; RRTUNCLUSTINSPCT_IND = 0 ;

RRTDISORGLOBPCT = 5 ; RRTDISORGLOBPCT_IND = 0 ;

RRIAPPENDINSERTPCT = 5 ; RRIAPPENDINSERTPCT_IND = 0 ;

SRTINSDELUPDPCT = 5 ; SRTINSDELUPDPCT_IND = 0 ;

SRIINSDELPCT = 5 ; SRIINSDELPCT_IND = 0 ;

EXTENTLIMIT = 3 ; EXTENTLIMIT_IND = 0 ;

RRTMASSDELLIMIT = 0 ; RRTMASSDELLIMIT_IND = -1 ;

RRIMASSDELLIMIT = 0 ; RRIMASSDELLIMIT_IND = -1 ;

LASTSTATEMENT = COPIES("",8012);

LASTSTATEMENT_IND = 0;

RETURNCODE = 0; RETURNCODE_IND = 0;

ERRORMSG = COPIES("",1331);

ERRORMSG_IND = 0;

IFCARETCODE = 0; IFCARETCODE_IND = 0;

IFCARESCODE = 0; IFCARESCODE_IND = 0;

XSBYTES = 0; XSBYTES_IND = 0;

/*********************************************************************/

/* call DB2 stored procedure */

/*********************************************************************/

sqlstmt = "CALL SYSPROC.DSNACCOX",

"(:QUERYTYPE :QUERYTYPE_IND, " ,

" :OBJECTTYPE :OBJECTTYPE_IND, " ,

" :ICTYPE :ICTYPE_IND, " ,

" :CATLGSCHEMA :CATLGSCHEMA_IND, " ,

" :LOCALSCHEMA :LOCALSCHEMA_IND, " ,

" :CHKLVL :CHKLVL_IND, " ,

" :CRITERIA :CRITERIA_IND, " ,

" :UNUSED :UNUSED_IND, " ,

" :CRUPDATEDPAGESPCT :CRUPDATEDPAGESPCT_IND, " ,

" :CRUPDATEDPAGESABS :CRUPDATEDPAGESABS_IND, " ,

" :CRCHANGESPCT :CRCHANGESPCT_IND, " ,

" :CRDAYSNCLASTCOPY :CRDAYSNCLASTCOPY_IND, " ,

" :ICRUPDATEDPAGESPCT :ICRUPDATEDPAGESPCT_IND, " ,

" :ICRUPDATEDPAGESABS :ICRUPDATEDPAGESABS_IND, " ,

" :ICRCHANGESPCT :ICRCHANGESPCT_IND, " ,

" :CRINDEXSIZE :CRINDEXSIZE_IND, " ,

" :RRTINSERTSPCT :RRTINSERTSPCT_IND, " ,

" :RRTINSERTSABS :RRTINSERTSABS_IND, " ,

" :RRTDELETESPCT :RRTDELETESPCT_IND, " ,

" :RRTDELETESABS :RRTDELETESABS_IND, " ,

" :RRTUNCLUSTINSPCT :RRTUNCLUSTINSPCT_IND, " ,

" :RRTDISORGLOBPCT :RRTDISORGLOBPCT_IND, " ,

" :RRTDATASPACERAT :RRTDATASPACERAT_IND, " ,

" :RRTMASSDELLIMIT :RRTMASSDELLIMIT_IND, " ,

" :RRTINDREFLIMIT :RRTINDREFLIMIT_IND, " ,

" :RRIINSERTSPCT :RRIINSERTSPCT_IND, " ,

" :RRIINSERTSABS :RRIINSERTSABS_IND, " ,

" :RRIDELETESPCT :RRIDELETESPCT_IND, " ,

" :RRIDELETESABS :RRIDELETESABS_IND, " ,

" :RRIAPPENDINSERTPCT :RRIAPPENDINSERTPCT_IND, " ,

" :RRIPSEUDODELETEPCT :RRIPSEUDODELETEPCT_IND, " ,

" :RRIMASSDELLIMIT :RRIMASSDELLIMIT_IND, " ,

" :RRILEAFLIMIT :RRILEAFLIMIT_IND, " ,

" :RRINUMLEVELSLIMIT :RRINUMLEVELSLIMIT_IND, " ,

" :SRTINSDELUPDPCT :SRTINSDELUPDPCT_IND, " ,

" :SRTINSDELUPDABS :SRTINSDELUPDABS_IND, " ,

" :SRTMASSDELLIMIT :SRTMASSDELLIMIT_IND, " ,

" :SRIINSDELUPDPCT :SRIINSDELUPDPCT_IND, " ,

" :SRIINSDELUPDABS :SRIINSDELUPDABS_IND, " ,

" :SRIMASSDELLIMIT :SRIMASSDELLIMIT_IND, " ,

" :EXTENTLIMIT :EXTENTLIMIT_IND, " ,

" :LASTSTATEMENT :LASTSTATEMENT_IND, " ,

" :RETURNCODE :RETURNCODE_IND, " ,

" :ERRORMSG :ERRORMSG_IND, " ,

" :IFCARETCODE :IFCARETCODE_IND, " ,

" :IFCARESCODE :IFCARESCODE_IND, " ,

" :XSBYTES :XSBYTES_IND)";

ADDRESS DSNREXX "CONNECT" ssid

call Code 0

address DSNREXX "EXECSQL" sqlstmt

call Code 0 466 /* +466 = more rows to fetch */

if returncode = 0

then do

/* say copies("=",79) */

/* say "Approximate "space(delword(errormsg,1,1)), */

/* "of the total number of objects in the subsystem that", */

/* "have information in the real-time statistics tables." */

/* say copies("=",79) */

/* say "" */

say "Recommendations: You should watch ..."

say copies("-",79)

end

else do

if returncode = 4

then Say "DSNACCOX completed, but with incompatible parms."

if returncode = 8

then say "DSNACCOX terminated with errors."

if returncode = 12

then say "DSNACCOX terminated with severe errors."

if returncode = 14

then say "You need to create the RTS tables or grant access."

if returncode = 16

then say "You need to create a TEMP database and tablespaces."

say "Error Message......: "errormsg

say "IFCA Return Code...: "IFCARetCode

say "IFCA Reseas Code...: "IFCAResCode

say "Last Statement.....: "laststatement

signal error

end

/*********************************************************************/

/* Combine two locators with the stored procedure */

/*********************************************************************/

LOC1 = d2x(0)

LOC2 = d2x(0)

sqlstmt = "ASSOCIATE LOCATORS (:LOC1, :LOC2)",

"WITH PROCEDURE SYSPROC.DSNACCOX"

address DSNREXX "EXECSQL" sqlstmt;

call code 0

/*********************************************************************/

/* Allocate and process cursor-driven */

/*********************************************************************/

sqlstmt = "ALLOCATE C101 CURSOR FOR RESULT SET :LOC1"

address DSNREXX "EXECSQL" sqlstmt;

do while sqlcode = 0

call code 0

sqlstmt = "FETCH C101 INTO :RS_SEQ, :RS_DATA"

Address DSNREXX "EXECSQL" sqlstmt;

/*say RS_SEQ RS_DATA*/

end

call code 0 100

sqlstmt = "CLOSE C101"

ADDRESS DSNREXX "EXECSQL "sqlstmt;

call code 0 100

/*********************************************************************/

/* Allocate and process cursor-driven */

/*********************************************************************/

sqlstmt = "ALLOCATE C102 CURSOR FOR RESULT SET :LOC2"

address DSNREXX "EXECSQL" sqlstmt;

call Code 0

/*********************************************************************/

/* Fetch all messages via the cursor */

/*********************************************************************/

sqlstmt = "FETCH C102 INTO ",

":DBNAME :DBNAME_IND ,",

":NAME :NAME_IND ,",

":PARTITION :PARTITION_IND ,",

":INSTANCE :INSTANCE_IND ,",

":CLONE :CLONE_IND ,",

":OBJECTTYPE :OBJECTTYPE_IND ,",

":INDEXSPACE :INDEXSPACE_IND ,",

":CREATOR :CREATOR_IND ,",

":OBJECTSTATUS :OBJECTSTATUS_IND ,",

":IMAGECOPY :IMAGECOPY_IND ,",

":RUNSTATS :RUNSTATS_IND ,",

":EXTENTS :EXTENTS_IND ,",

":REORG :REORG_IND ,",

":INEXCEPTTABLE :INEXCEPTTABLE_IND ,",

":ASSOCIATEDTS :ASSOCIATEDTS_IND ,",

":COPYLASTTIME :COPYLASTTIME_IND ,",

":LOADRLASTTIME :LOADRLASTTIME_IND ,",

":REBUILDLASTTIME :REBUILDLASTTIME_IND ,",

":CRUPDPGSPCT :CRUPDPGSPCT_IND ,",

":CRUPDPGSABS :CRUPDPGSABS_IND ,",

":CRCPYCHGPCT :CRCPYCHGPCT_IND ,",

":CRDAYSCELSTCPY :CRDAYSCELSTCPY_IND ,",

":CRINDEXSIZE :CRINDEXSIZE_IND ,",

":REORGLASTTIME :REORGLASTTIME_IND ,",

":RRTINSERTSPCT :RRTINSERTSPCT_IND ,",

":RRTINSERTSABS :RRTINSERTSABS_IND ,",

":RRTDELETESPCT :RRTDELETESPCT_IND ,",

":RRTDELETESABS :RRTDELETESABS_IND ,",

":RRTUNCINSPCT :RRTUNCINSPCT_IND ,",

":RRTDISORGLOBPCT :RRTDISORGLOBPCT_IND ,",

":RRTDATASPACERAT :RRTDATASPACERAT_IND ,",

":RRTMASSDELETE :RRTMASSDELETE_IND ,",

":RRTINDREF :RRTINDREF_IND ,",

":RRIINSERTPCT :RRIINSERTPCT_IND ,",

":RRIINSERTABS :RRIINSERTABS_IND ,",

":RRIDELETEPCT :RRIDELETEPCT_IND ,",

":RRIDELETABS :RRIDELETABS_IND ,",

":RRIAPPINSPCT :RRIAPPINSPCT_IND ,",

":RRIPSDDELPCT :RRIPSDDELPCT_IND ,",

":RRIMASSDELETE :RRIMASSDELETE_IND ,",

":RRILEAF :RRILEAF_IND ,",

":RRINUMLEVELS :RRINUMLEVELS_IND ,",

":STATSLASTTIME :STATSLASTTIME_IND ,",

":SRTINSDELUPDPCT :SRTINSDELUPDPCT_IND ,",

":SRTINSDELUPDABS :SRTINSDELUPDABS_IND ,",

":SRTMASSDELETE :SRTMASSDELETE_IND ,",

":SRIINSDELPCT :SRIINSDELPCT_IND ,",

":SRIINSDELABS :SRIINSDELABS_IND ,",

":SRIMASSDELETE :SRIMASSDELETE_IND ,",

":TOTALEXTENTS :TOTALEXTENTS_IND ";

do while sqlcode = 0

if OBJECTTYPE <> "ALL"

then do

Say "DATABASE: "DBNAME" SPACENAME: "NAME "("OBJECTTYPE")"

if IMAGECOPY <> "NO"

then do

if COPYLASTTIME_IND = -1 then COPYLASTTIME = "NEVER"

say "- Imagecopy ...: "IMAGECOPY", last done "COPYLASTTIME

call Line(CRUPDPGSPCT "% updated pages")

call Line(CRCPYCHGPCT "% rows INS/DEL/UPD")

call Line(CRDAYSCELSTCPY "day(s) since last COPY")

end

if RUNSTATS <> "NO"

then do

if STATSLASTTIME_IND = -1 then STATSLASTTIME = "NEVER"

say "- Runstats ....: "RUNSTATS", last done "STATSLASTTIME

call Line(SRTMASSDELETE "mass deleted")

call Line(SRTINSDELUPDPCT "% rows INS/DEL/UPD")

end

if REORG <> "NO"

then do

if REORGLASTTIME_IND = -1 then REORGLASTTIME = "NEVER"

say "- Reorg .......: "REORG", last done "REORGLASTTIME

call Line(RRTMASSDELETE "mass deleted")

call Line(RRTINSERTSPCT "% rows INS/DEL/UPD")

call line(RRTUNCINSPCT "% rows unclustered INSERT's")

call line(RRTINDREF "% overflowed records")

end

if EXTENTS <> "NO"

then do

say "- Extents .....: "EXTENTS", "TOTALEXTENTS" extent(s)"

end

Address DSNREXX "EXECSQL" SQLSTMT

end

call code 0 100

sqlstmt = "CLOSE C102"

address DSNREXX "EXECSQL "sqlstmt;

call code 0 100

say "*** end ***"

/*********************************************************************/

/* Disconnect vom Subsystem */

/*********************************************************************/

Disconnect:

ADDRESS DSNREXX "DISCONNECT"

cc = RXSUBCOM("DELETE","DSNREXX","DSNREXX")

return

/*********************************************************************/

/* Error processing and termination */

/*********************************************************************/

SYNTAX:

say "XMPACCOX: Syntax error."

say " [TSO] XMPACCOX ssid options"

say " ssid .......: DB2 Subsystem ID"

say " criteria ...: criteria for DSNACCOX"

ERROR:

say ""

say "Current enviroment:" jobnamestep source_host_env,

source_call_type source_uc_exec_name source_hostdd,

source_hostdsn source_nf_exec_name source_initial_cmd_env

If Sysvar(SYSISPF) = "NOT ACTIVE"

then do

say "REXX procedure XMPACCOX ends with U0001."

address ATTACH "XMPABND"

exit 0

end

say "REXX procedure XMPACCOX ends with RC=20."

exit 20

/*********************************************************************/

/* Check SQLCODE's allowed */

/*********************************************************************/

Code:

arg codes

allowed = "NO"

do ci=1 to words(codes)

if sqlcode = word(codes,ci)

then allowed = "YES"

end

if allowed = "NO"

then do

say "CONNECT: SQL Error with SQLCODE="sqlcode

call TIAR sqlcode

say " "sqlstmt

call Disconnect

signal error

end

return

/*********************************************************************/

/* process line output, hide if value contains no information */

/*********************************************************************/

Line: arg lval ltext

lval = trunc(lval)

if lval > 0

then say right(lval,20)" "ltext

return

/*********************************************************************/

/* Retrieve some internal information from z/OS control blocks */

/*********************************************************************/

GET_JOBNAME: Procedure

/* all fields are part of general use prog'ing interface */

PSATOLD = '21C'X

TCB = C2D(STORAGE(C2X(PSATOLD),4))

TIOT = C2D(STORAGE(D2X(TCB+12),4))

TIOCNJOB = STRIP(STORAGE(D2X(TIOT),8))

TIOCSTEP = STRIP(STORAGE(D2X(TIOT+8),8))

Return TIOCNJOB TIOCSTEP

/*********************************************************************/

/* Display SQL Commmunication area using DSNTIAR */

/*********************************************************************/

TIAR: arg sqlcode

sqlc = d2x(sqlcode,8)

sqlc = x2c(sqlc)

sqlca = 'sqlca '

sqlca = sqlca !! x2c(00000088)

sqlca = sqlca !! sqlc

sqlca = sqlca !! x2c(0000)

sqlca = sqlca !! copies(' ',78)

sqlca = sqlca !! copies(x2c(00),24)

sqlca = sqlca !! copies(' ',16)

tiar_msg = x2c(0190)copies(' ',400)

text_len = x2c(00000050) ;

/***************************************************************/

/* Extract Message from Return Area */

/***************************************************************/

ADDRESS ATTCHPGM 'DSNTIAR SQLCA TIAR_MSG TEXT_LEN'

say substr(tiar_msg,4,400);

/***************************************************************/

/* Extract Message from Return Area */

/***************************************************************/

len = c2d(substr(rtrnarea,5,2))

/***************************************************************/

/* Extract Message from Return Area */

/***************************************************************/

say " SQLCODE="sqlcode

say " SQLERRMC="sqlerrmc

say " SQLWARN5="sqlwarn.5

say " SQLERRP ="sqlerrp

say " SQLSTATE="sqlstate

return

A possible result looks like …
############################################################################### # XMPACCOX V2.0 - Recommendations to maintain DB2 objects 11/02/17 19:25:30 # ###############################################################################

Executing without arguments, setting default DB2 subsystem id and criteria.
XMPACCOX …: Following values have been set for DSNACCOX
DB2 SSID …: DB2T
CHKLVL …..: Use the chklvl parameter to cause dsnaccox to
check for related table spaces when processing
index spaces, and delete recommendation for
indexspaces when an action (such as reorg) on the
table space will also cause the action to be done
on the index space. (CHKLVL=64)
CRITERIA …: DBNAME LIKE ‘DSN%’ AND NAME LIKE ‘%’
Use the criteria parameter to cause DSNACCOX to
make recommendations only for objects in databases
you want to.
Other parms : Following parameter values have been set, which are
lower than the defaults:
CRUPDATEDPAGESPCT 4 RRIAPPENDINSERTPCT 5
CRCHANGESPCT 2 SRTINSDELUPDPCT 5
RRTINSERTPCT 2 SRIINSDELPCT 5
RRTUNCLUSTINSPCT 5 EXTENTLIMIT 3
RRTDISORGLOBPCT 5
Excluded the checking for these criteria by set the
following values to a negative value:
RRTMASSDELLIMIT -1 RRIMASSDELLIMIT -1

Recommendations: You should watch …
——————————————————————————-
DATABASE: DSN8D81A SPACENAME: DSN8S81D (TS)
- Imagecopy …: FULL, last done 0001-01-01-00.00.00.000000
8 % UPDATED PAGES
28 % ROWS INS/DEL/UPD
734549 DAY(S) SINCE LAST COPY
DATABASE: DSN8D81A SPACENAME: DSN8S81E (TS)
- Imagecopy …: FULL, last done NEVER
DATABASE: DSN8D81A SPACENAME: DSN8S81E (TS)
- Imagecopy …: FULL, last done NEVER
DATABASE: DSN8D81A SPACENAME: DSN8S81E (TS)
- Imagecopy …: FULL, last done NEVER
DATABASE: DSN8D81A SPACENAME: DSN8S81E (TS)
- Imagecopy …: FULL, last done NEVER
DATABASE: DSN8D81A SPACENAME: DSN8S81E (TS)
- Imagecopy …: FULL, last done NEVER
DATABASE: DSN8D81A SPACENAME: DSN8S81P (TS)
- Imagecopy …: FULL, last done 0001-01-01-00.00.00.000000
734549 DAY(S) SINCE LAST COPY
DATABASE: DSN8D81A SPACENAME: DSN8S81R (TS)
- Imagecopy …: FULL, last done 0001-01-01-00.00.00.000000
734549 DAY(S) SINCE LAST COPY
- Reorg …….: YES, last done 0001-01-01-00.00.00.000000
DATABASE: DSN8D81A SPACENAME: DSN8S81S (TS)
- Imagecopy …: FULL, last done 0001-01-01-00.00.00.000000
734549 DAY(S) SINCE LAST COPY
- Reorg …….: YES, last done 0001-01-01-00.00.00.000000
DATABASE: DSN8D81E SPACENAME: DSN8S81U (TS)
- Imagecopy …: FULL, last done 0001-01-01-00.00.00.000000
734549 DAY(S) SINCE LAST COPY
DATABASE: DSN8D81P SPACENAME: DSN8S81C (TS)
- Imagecopy …: FULL, last done 0001-01-01-00.00.00.000000
734549 DAY(S) SINCE LAST COPY
DATABASE: DSN8D81P SPACENAME: DSN8S81Q (TS)
- Imagecopy …: FULL, last done 0001-01-01-00.00.00.000000
734549 DAY(S) SINCE LAST COPY
DATABASE: DSN8D81U SPACENAME: NEWDEPT (TS)
- Imagecopy …: FULL, last done NEVER
- Reorg …….: YES, last done 2008-12-16-15.00.33.305895
100 % ROWS INS/DEL/UPD
DATABASE: DSN8D81U SPACENAME: NEWPHONE (TS)
- Imagecopy …: FULL, last done NEVER
- Reorg …….: YES, last done 2008-12-16-15.00.33.712209
100 % ROWS INS/DEL/UPD
DATABASE: DSNADMDB SPACENAME: DSNADMTH (TS)
- Runstats ….: YES, last done NEVER
DATABASE: DSNATPDB SPACENAME: DSNASDMA (TS)
- Reorg …….: YES, last done NEVER
DATABASE: DSNATPDB SPACENAME: DSNASDME (TS)
- Reorg …….: YES, last done NEVER
DATABASE: DSNATPDB SPACENAME: DSNASDMU (TS)
- Reorg …….: YES, last done NEVER
DATABASE: DSNATPDB SPACENAME: DSNATPTS (TS)
- Reorg …….: YES, last done NEVER
DATABASE: DSNDB04 SPACENAME: TSQ18925 (TS)
- Imagecopy …: FULL, last done NEVER
- Runstats ….: YES, last done NEVER
DATABASE: DSNDB04 SPACENAME: TSQ41683 (TS)
- Imagecopy …: FULL, last done NEVER
- Runstats ….: YES, last done NEVER
DATABASE: DSNDB04 SPACENAME: TSQ89405 (TS)
- Imagecopy …: FULL, last done NEVER
- Runstats ….: YES, last done NEVER
DATABASE: DSNDB06 SPACENAME: SYSALTER (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.16.724754
586 % ROWS INS/DEL/UPD
- Reorg …….: YES, last done 2011-10-29-14.11.27.622702
43 % ROWS INS/DEL/UPD
DATABASE: DSNDB06 SPACENAME: SYSCONTX (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.17.032202
300 % ROWS INS/DEL/UPD
- Reorg …….: YES, last done 2011-10-29-14.16.32.605912
DATABASE: DSNDB06 SPACENAME: SYSCOPY (TS)
- Imagecopy …: INC , last done 2012-02-17-06.07.18.872718
- Runstats ….: YES, last done 2010-08-23-11.54.18.580229
1029 % ROWS INS/DEL/UPD
- Reorg …….: YES, last done 2011-10-29-14.11.34.498504
114 % ROWS INS/DEL/UPD
7 % ROWS UNCLUSTERED INSERT’S
DATABASE: DSNDB06 SPACENAME: SYSDBASE (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.19.636099
274 % ROWS INS/DEL/UPD
DATABASE: DSNDB06 SPACENAME: SYSDBAUT (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.27.024685
65 % ROWS INS/DEL/UPD
- Reorg …….: YES, last done 2011-10-29-14.12.27.742294
DATABASE: DSNDB06 SPACENAME: SYSDDF (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.27.699100
13 % ROWS INS/DEL/UPD
- Reorg …….: YES, last done 2011-10-29-14.12.44.538830
DATABASE: DSNDB06 SPACENAME: SYSEBCDC (TS)
- Reorg …….: YES, last done NEVER
DATABASE: DSNDB06 SPACENAME: SYSGPAUT (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.28.917269
29 % ROWS INS/DEL/UPD
DATABASE: DSNDB06 SPACENAME: SYSGROUP (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.29.166183
76 % ROWS INS/DEL/UPD
- Reorg …….: YES, last done 2011-10-29-14.12.56.197333
DATABASE: DSNDB06 SPACENAME: SYSGRTNS (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.29.747266
DATABASE: DSNDB06 SPACENAME: SYSHIST (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.30.205863
111 % ROWS INS/DEL/UPD
- Reorg …….: YES, last done 2011-10-29-14.13.12.652053
42 % ROWS INS/DEL/UPD
DATABASE: DSNDB06 SPACENAME: SYSJAUXA (TS)
- Reorg …….: YES, last done NEVER
DATABASE: DSNDB06 SPACENAME: SYSJAUXB (TS)
- Reorg …….: YES, last done NEVER
DATABASE: DSNDB06 SPACENAME: SYSJAVA (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.31.545214
DATABASE: DSNDB06 SPACENAME: SYSOBJ (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.32.159832
161 % ROWS INS/DEL/UPD
- Reorg …….: YES, last done 2011-10-29-14.13.44.983536
31 % ROWS INS/DEL/UPD
DATABASE: DSNDB06 SPACENAME: SYSPKAGE (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.36.461244
335 % ROWS INS/DEL/UPD
- Reorg …….: YES, last done 2011-10-29-14.14.05.817685
34 % ROWS INS/DEL/UPD
DATABASE: DSNDB06 SPACENAME: SYSPLAN (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.38.156912
243 % ROWS INS/DEL/UPD
- Reorg …….: YES, last done 2011-10-29-14.14.13.682557
32 % ROWS INS/DEL/UPD
DATABASE: DSNDB06 SPACENAME: SYSPLUXA (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.38.515056
15700 % ROWS INS/DEL/UPD
- Reorg …….: YES, last done 2011-10-29-14.16.00.877993
3000 % ROWS INS/DEL/UPD
DATABASE: DSNDB06 SPACENAME: SYSROLES (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.38.630576
DATABASE: DSNDB06 SPACENAME: SYSSEQ (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.40.091097
96 % ROWS INS/DEL/UPD
- Reorg …….: YES, last done 2011-10-29-14.14.20.156097
DATABASE: DSNDB06 SPACENAME: SYSSEQ2 (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.40.556231
94 % ROWS INS/DEL/UPD
- Reorg …….: YES, last done 2011-10-29-14.14.26.592014
DATABASE: DSNDB06 SPACENAME: SYSSTATS (TS)
- Imagecopy …: INC , last done 2012-02-17-06.07.07.620002
- Runstats ….: YES, last done 2010-08-23-11.54.40.996292
7221 % ROWS INS/DEL/UPD
- Reorg …….: YES, last done 2011-10-29-14.14.58.985117
2006 % ROWS INS/DEL/UPD
1451 % ROWS UNCLUSTERED INSERT’S
DATABASE: DSNDB06 SPACENAME: SYSSTR (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.41.821443
94 % ROWS INS/DEL/UPD
- Reorg …….: YES, last done 2011-10-29-14.15.07.509454
49 % ROWS INS/DEL/UPD
6 % ROWS UNCLUSTERED INSERT’S
DATABASE: DSNDB06 SPACENAME: SYSTARG (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.42.414679
1900 % ROWS INS/DEL/UPD
- Reorg …….: YES, last done 2011-10-29-14.15.49.328602
900 % ROWS INS/DEL/UPD
DATABASE: DSNDB06 SPACENAME: SYSUSER (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.42.699836
8 % ROWS INS/DEL/UPD
DATABASE: DSNDB06 SPACENAME: SYSVIEWS (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.44.840788
84 % ROWS INS/DEL/UPD
DATABASE: DSNDB06 SPACENAME: SYSXML (TS)
- Runstats ….: YES, last done 2010-08-23-11.54.46.074462
212 % ROWS INS/DEL/UPD
- Reorg …….: YES, last done 2011-10-29-14.15.43.445283
150 % ROWS INS/DEL/UPD
DATABASE: DSNOSCDB SPACENAME: DSNMQLTS (TS)
- Runstats ….: YES, last done NEVER
DATABASE: DSNOSCDB SPACENAME: DSNOSCL1 (TS)
- Runstats ….: YES, last done NEVER
DATABASE: DSNOSCDB SPACENAME: DSNOSCTS (TS)
- Reorg …….: YES, last done 2010-07-20-09.52.42.282010
520 % ROWS INS/DEL/UPD
DATABASE: DSNRLST SPACENAME: DSNRLS01 (TS)
- Reorg …….: YES, last done NEVER
DATABASE: DSN8D81A SPACENAME: XEMP1 (IX)
- Reorg …….: YES, last done NEVER
DATABASE: DSN8D81A SPACENAME: XEMP1 (IX)
- Reorg …….: YES, last done NEVER
DATABASE: DSN8D81A SPACENAME: XEMP1 (IX)
- Reorg …….: YES, last done NEVER
DATABASE: DSN8D81A SPACENAME: XEMP2 (IX)
- Reorg …….: YES, last done NEVER
DATABASE: DSN8D81A SPACENAME: XEMP1 (IX)
- Reorg …….: YES, last done NEVER
DATABASE: DSN8D81A SPACENAME: XEMP1 (IX)
- Reorg …….: YES, last done NEVER
DATABASE: DSNADMDB SPACENAME: ADMIN_TASKS_IX (IX)
- Imagecopy …: YES , last done NEVER
DATABASE: DSNADMDB SPACENAME: ADMIN_TASKS_HIST_IX (IX)
- Imagecopy …: YES , last done NEVER
DATABASE: DSNDB06 SPACENAME: DSNACX01 (IX)
- Reorg …….: YES, last done 2011-10-29-14.12.17.780825
DATABASE: DSNDB06 SPACENAME: DSNDXX01 (IX)
- Reorg …….: YES, last done 2011-10-29-14.12.17.780470
DATABASE: DSNDB06 SPACENAME: DSNDRX01 (IX)
- Reorg …….: YES, last done 2011-10-29-14.12.17.780618
DATABASE: DSNDB06 SPACENAME: DSNDKX01 (IX)
- Reorg …….: YES, last done 2011-10-29-14.12.17.780677
DATABASE: DSNDB06 SPACENAME: DSNDYX01 (IX)
- Reorg …….: YES, last done 2011-10-29-14.12.17.780854
DATABASE: DSNDB06 SPACENAME: DSNATX03 (IX)
- Reorg …….: YES, last done 2011-10-29-14.12.17.780766
DATABASE: DSNDB06 SPACENAME: DSNATX02 (IX)
- Reorg …….: YES, last done 2011-10-29-14.12.17.780737
DATABASE: DSNDB06 SPACENAME: DSNDPX01 (IX)
- Reorg …….: YES, last done 2011-10-29-14.12.17.780040
DATABASE: DSNDB06 SPACENAME: DSNDPX03 (IX)
- Reorg …….: YES, last done 2011-10-29-14.12.17.780108
DATABASE: DSNDB06 SPACENAME: DSNDTX02 (IX)
- Reorg …….: YES, last done 2011-10-29-14.12.17.780253
*** end ***

DB2 Space calculation – from pages to tracks

All system z DASD I/O systems base on the IBM DASD device type IBM 3390 Model 3 and are compatible to it. This means that all calculations a DB2 DBA or sysadm can to do are on basis of this type and model characteristics. Quite typical for this DASD model: 12 pages of DB2 data fit into 1 track. For older types, such as IBM 3380, which are not considered here, only 10 pages to fit into a track. More about DB2 data and storage, see below.
IBM Type 3390 Model 3
56664 bytes per track 849960 bytes per cylinder 15 tracks per cylinder 3339 cylinders per device	3339 cylinders x 15 trk/cyl = 50100 tracks/device	2838 MB/Device approx. 2.75 GB
DB2 space calcalution on basis of device type 3390
smallest unit DB2 can externalize: 1 page with 4 KB, which is 4096 bytes	1 track = 56664 bytes overhead per block of records stored = 340 Bytes	56664 / (4096 + 340) = 12.773 which is 12 pages per track
12 pages = 12 x 4 KB = 48 KB net user data = 48 KB which claim 55 KB space on DASD device DB2 data + 15% overhead = total usage of DASD storag	1 cylinder = 15 rracks x 12 pages = 180 pages = 720 KB	1 Device = 50100 tracks = 601200 pages = 2.4 GB DB2 net user data

CICS and MQ Explorer

IBM CICS Explorer: Common, intuitive, Eclipse-based environment for architects, developers, administrators, system programmers, and operators. Looking for new ways to accelerate the transfer of knowledge, skills, and best practice to the next generation of technical staff/experts? Need to maintain productivity and protect service-levels? CICS Explorer and System z lead the way to platform simplification.

Download

IBM CICS Explorer Quick Reference

IBM’s MQ Explorer is a free graphical configuration tool built on Eclipse which enables you to remotely explore and configure all WebSphere MQ objects and resources, including Java Message Service (JMS), and publish and subscribe. MQ Explorer can remotely connect to queue managers on any supported platform – enabling your entire messaging backbone to be viewed, explored and altered from one place. MQ Explorer runs on Windows and Linux (x86 and x86_64) systems. It does not need to be deployed with a WebSphere MQ client or server, and can be installed on as many machines as you require.

Download

Perl Script xmpBkp.pl – DB2 Backup, Restore, List Backup and Backup Script Creation

Everything you need for total control of your DB2 backup process:

#!/usr/local/bin/perl -w
################################################################################
# xmpBkp.pl - DB2 Full Backup Creation, Restore and List Tool
# OS System - AIX 4.3.x or Sun/Solaris 2.6 or higher
# Database  - DB2 UDB V5.x or higher, works with DB2 V7.x
# Called by - Command Level or DBA Scripts
# Calls     - xmpPerl.pm   - xmp DBA Perl Package
#             Getopt       - Arguments processing 
#             Env          - Access on environment variables 
#             English      - Aliases on Perl variables
#             Cwd          - Current Working directory;
#             DBI		   - Database Interface
#             DBD::DB2     - DB2 UDB Interface
# Options   - -d database  - Database Name
#             -a ...       - Action: BACKUP [INCREMENTAL]/LIST/CHECK/RESTORE
#             -f path      - Path to Backup File
#             -g ...       - backup file generation(s) to keep or to restore
#-------------------------------------------------------------------------------
# Autor     - GR /18.09.01 - V1.0 first version 
# Changes   - GR /12.10.01 - V1.1 now includes CHECK housekeeping function
#             GR /17.10.01 - V1.2 now includes RESTORE function
#             GR /06.11.01 - V1.3 File Maintenance in advance instead of afterwards,
#                                 user/psw not required
#             GR /09.11.01 - V1.3.1 Start/Stop Timestamp inserted, problems with
#                                   stat module (printting size, uid deactivated)
#             GR /09.11.01 - V1.3.2 Line Feed inserted
#             GR /26.02.02 - V1.4 Corrected comments for calling the script,
#                                 show and release unregistered (obsolete) bkps.
#             GR /27.03.02 - V2.0 Incremental Backups and Restore
#             GR /15.04.02 - V2.1.1 Different handling of -g argument
#             GR /05.07.02 - V2.2 Process only Backup/Restore/Rollfwd Events
#             GR /09.07.02 - V2.2.1 remaining calls of stat module deactivated 
#             GR /28.08.02 - V2.3 Set signal/return code if backup check failed
#             GR /20.01.03 - V2.4 Show threads if DB in use (i.e. SQL1035N),
#                                 added support for .gz files
#             GR /03.03.04 - V2.5 Parm -x NOBUFF to suppress buffering
#             GR /05.05.04 - V2.6 GetThreads copied from xmpTerm.pl
#             GR /05.01.05 - V3.0 Handles multiple backup files
#             GR /10.01.05 - V3.0 New -compress option
#             GR /17.01.05 - V3.0.1 Sequence of DB2 backup command options corrected
#             GR /28.04.05 - V3.1 Deflt=no buffering, BUFF+COMPRESS are incompatible!,
#                                 applying both results in "SQL2009C There is not enough memory available",
#                                 -x may contain any additional backup parm.
#                                 Fixed problem with registered but not existing file when backup canceled.
#             GR /25.10.06 - V4.0 Made COMPRESS'ed backups to the default,
#								  established DB2 environment, use DB2 table 
#								  functions instead of LIST command output, new
#                                 parm SYMBLINK for alternate Symbolic Link File Name,
#                                 parm HARDLINK for alternate Hard Link File Name.
#             GR /17.11.06 - V4.1 Provide more information of backup groups.
#                                 New strategy for links: attach link id to original name.
#             GR /20.11.06 - V4.1.1 Correction of SQL statement, using coalesce 
#             GR /13.04.07 - V4.2 New action SCRIPT to create RESTORE script file
#             GR /29.05.07 - V4.2.1 More information about created RESTORE script file
#             GR /25.07.08 - V4.2.2 BACKUP FULL and BACKUP DELTA=incremental added  
#             GR /29.07.08 - V4.3 Adaptions for DB2 V9.7
#             GR /05.08.11 - V4.3.1 Small amendments
#             GR /05.08.11 - V4.3.2 Verify opt_g to be positive on BACKUP
#-------------------------------------------------------------------------------
# Comments  - PERL5LIB environment variable must point to perl module path!
#           - Works only with OFFLINE backups!
#           - Script shares sub-routines with xmpRHFTool.pl!
################################################################################

################################################################################
# Modules 
################################################################################
use strict;
use Getopt::Long;										# V3.0
use vars qw($opt_d $opt_u $opt_p $opt_a $opt_f $opt_g $opt_x);
use vars qw($opt_span);									# V3.0
use vars qw($opt_compress);								# V3.0
use vars qw($opt_symblink $opt_hardlink);				# V4.0
use xmpPerl;
use Env;
use English;
use File::stat;
use File::Basename;
use Time::localtime;
use Cwd;												# V4.0

use DBI;
use DBD::DB2::Constants;
use DBD::DB2;

################################################################################
# Declare/Init 
################################################################################
my $VER     = "4.3.2";
my $work    = "";
my @work;

my $n_bkp_unregistered = 0;						# no. of unregistered backups
my $dm		= 0;								# Feld für Dummy Werte
my $bkpfile;									# Feld für Log Dateien

my $f_on = 0;									# Flag on/off
my $n = 0;										# Counter
my $i = 0;										# Counter
my $ino;										# File Info Block

my $DB2Version  = xmpPerl::getDB2Version;		# actual version of DB2
my $dbcfg_logpath = "";
my $dbcfg_nextlog = "";
my $dbcfg_firstlog = "";
my $dbcfg_logretain = "";
my $dbcfg_userexit = "";
my $dbcfg_logfilsiz = "";
my $dbcfg_logprimary = "";
my $dbcfg_consistent = "";

my $add_options = "";							# V3.1 additional options

my $span_files = "";							# V3.0 spanned bkp files

my ($dbh, $sth);								# DB2 Access Handles
my $stmt	= "";								# DB2 Statement

################################################################################
# Declare/Init: Event Table
################################################################################
my $e = 0;										# Database Event Table
my %e_oper = ();	my $c1;                    	# Operation
my %e_obj = ();		my $c2;						# Object Type
my %e_ts = ();		my $c3;						# Timestamp
my %e_type = ();	my $c4;						# Backup Type
my %e_dev = ();		my $c5;						# Backup Device
my %e_1log = ();	my $c6;
my %e_clog = ();	my $c7;
my %e_path = ();	my $cb;						# Backup Path
my %e_bkp = ();		my $cd;						# Backup File exists?
my %e_start = ();	my $c9;
my %e_end = ();		my $ca;
my %e_comm = ();	my $cc;						# Comments/Commands
my %e_bkpfile =();								# Backup File

################################################################################
# Declare/Init: Thread Table
################################################################################
my $t		= 0;								# no. of Threads
my %t_authid    = (); my $f1="";				# Auth ID
my %t_applname  = (); my $f2="";				# Application Name
my %t_id	= (); 	  my $f3="";				# Thread ID
my %t_applid	= (); my $f4;					# Application ID
my %t_seq       = (); my $f5;					# Sequence
my %t_agentno   = (); my $f6;					# Number of Agents
my %t_node 	= (); 	  my $f7;					# Coordinator Node
my %t_pid	= (); 	  my $f8;					# Coordinator PID	
my %t_status	= (); my $f9;					# Status
my %t_statchgtm	= (); my $fa;					# Status Change Time
my %t_dbname	= (); my $fb;					# Database Name
my %t_path	= (); 	  my $fc;					# DB Path

################################################################################
# Declare/Init: Backup File Table
################################################################################
my $bf		= 0;								# no. of backup files
my %bf_name     = ();							# backup file name
my %bf_basename = ();							# backup file basename
my %bf_rhf      = ();							# registered in RHF

################################################################################
# Declare/Init: Backup Group Table (for spanned backups)		# V3.0
################################################################################
my $bg		= 0;								# no. of backup files
my %bg_name     = ();							# backup group name
my %bg_type     = ();							# backup group type
my %bg_start    = ();							# backup group start
my %bg_path     = ();							# backup group path
my %bg_bkp	= ();								# backup group files exist
my %bg_fc	= ();								# backup group exist. files count
my %bg_ec	= ();								# backup group event count

################################################################################
# Output Report Format
################################################################################
format KOPF = 
--------------------------------------------------------------------------------
+--Operation: B=BACKUP L=LOAD R=RESTORE S=RUNST T=ALTER D=DROP F=ROLLF G=REORG
! +--Object: D=DB P=TABLESPACE T=TABLE
! !                   +--Type: F=OFFLINE I=INCREMENT N=ONLINE C/R=ALTER-TS etc 
! !                   ! +--Device: A=ADSM D=DISK L=LOCAL P=PIPE T=TAPE S=SERVER
! !                   ! !  ?: X=BACKUP FILE EXISTS
! !                   ! * 
* *                   * D 1st Log        Curr Log       Path
P O Timestmp+Seq      T ? Start Time     End Time       Statement/Comment
--------------------------------------------------------------------------------
.

format ZEILE =
@ @ @<<<<<<<<<<<<<<<< @ @ @<<<<<<<<<<<   @<<<<<<<<<<<   @<<<<<<<<<<<<<<<<<<<<<<<
$c1,$c2,$c3,          $c4,$c5,$c6,       $c7,           $cb,
                        @ @<<<<<<<<<<<<< @<<<<<<<<<<<<< @<<<<<<<<<<<<<<<<<<<<<<<
		                $cd,$c9,         $ca,           $cc
.

################################################################################
# Report Format Options 
################################################################################
$FORMAT_NAME 		= "ZEILE"; 					# $~
$FORMAT_TOP_NAME 	= "KOPF";					# $^
$FORMAT_FORMFEED	= "";						# $^L 
$FORMAT_LINES_PER_PAGE	= 60;					# $=, plus Lines für Kopf

################################################################################
# Check Argumente/Options
################################################################################
#getopts("d:a:f:g:x:");							# V3.0
GetOptions("d=s","u:s","p:s","a=s","f:s","g:i","x:s","span:i","symblink:s","hardlink:s","compress:s","script:s");


if (defined $opt_a ) {							# Action Check
	$opt_a = uc $opt_a; }						# Action uppercase
else {
	print "$0 - option '-a action' missing.\n";
 	SaySyntax();
}


if (! defined $opt_d )							# Datenbank Check
	{print "$0 - Database not specified\n";
         SaySyntax();
	}
$opt_d = uc $opt_d;								# Database uppercase

if (! defined $opt_u )							# user check 	V4.0
	{$opt_u = xmpPerl::getDBAUser();
    if ($opt_u eq "")
		{print "$0 - user not specified and lP_LUS env variable not defined\n";
        	 SaySyntax();  }
	}
		
if (! defined $opt_p )							# User Password Check	V4.0
	{$opt_p = xmpPerl::getDBAPsw();
	if ($opt_p eq "")
		{print "$0 - missing user's password fehlt and lP_LPW env variable not defined\n";
        	 SaySyntax();  }
	}

if ( (defined $opt_a )    and					# Action Check
     ($opt_a ne "BACKUP") and
     ($opt_a ne "BACKUP FULL") and				# V4.2.2
     ($opt_a ne "BACKUP INCREMENTAL") and		# V2.0
     ($opt_a ne "BACKUP DELTA") and				# V4.2.2
     ($opt_a ne "RESTORE") and
     ($opt_a ne "SCRIPT") and	 				# V4.2
     ($opt_a ne "LIST")   and   
     ($opt_a ne "CHECK")  ) {		
	print "$0 - option '-a $opt_a' is not one of the valid actions.\n";
 	SaySyntax();
}

if ( (defined $opt_a )    and					# Action 
     (  ($opt_a eq "BACKUP") 
	 or ($opt_a eq "BACKUP FULL") 	 			# V4.2.2
	 or ($opt_a eq "BACKUP DELTA") 				# V4.2.2
     or ($opt_a eq "BACKUP INCREMENTAL")  )and 	# V2.0
     (! defined $opt_f)   ) {		
	print "$0 - option '-f path' needed for action BACKUP [FULL|DELTA|INCREMENTAL].\n";
 	SaySyntax();
}

if ( (defined $opt_a )    and					# Action SCRIPT			V4.2
     ($opt_a eq "SCRIPT") and
     (! defined $opt_f)   ) {		
	print "$0 - option '-f path' needed to create restore script file.\n";
 	SaySyntax();
}

if ((defined $opt_g )   and 					# Generation Check
    ($opt_g !~ /[1234567890]/) ) {				# not numererical
	print "$0 - option '-g $opt_g ' invalid, specify numerical value 0-9.\n";
    SaySyntax();
}

if ((defined $opt_g )   and 					# Generation Check
    ($opt_g lt "1")  	and 					# V4.3.2
    (   ($opt_a eq "BACKUP")
	 or ($opt_a eq "BACKUP FULL") 	 			# V4.2.2
	 or ($opt_a eq "BACKUP DELTA") 				# V4.2.2	
     or ($opt_a eq "BACKUP INCREMENTAL") ) ) {
	print "$0 - option '-g $opt_g ' invalid for action BACKUP [FULL|DELTA|INCREMENTAL]|SCRIPT.\n";
	print "(-g must be >0.) \n";
    SaySyntax();
}

if ((defined $opt_span )   and 						# Backup File spanning	V3.0
    ($opt_span !~ /[1234567890]/) ) {				# not numerical
	print "$0 - option '-span $opt_span' invalid, specify numerical value 0-9.\n";
    SaySyntax();
}

if ((defined $opt_span )   and 						# Backup File spanning	V3.0
    ($opt_span < 1) ) 		{						# must be >= 1
	print "$0 - option '-span $opt_span' invalid, must be >= 1.\n";
        SaySyntax();
}

if ((defined $opt_symblink )   and 					# Symbolic File Link V 4.0
    ($opt_symblink eq "") ) 		{			    # ignore blank
	undef $opt_symblink;
}

#if ((defined $opt_symblink) and 					# V4.1
#    (defined $opt_span)    )	{
#		die "==> Options -span and -symblink are mutually exclusive - please correct.\n".
#		"==> Script abends";
#}

if (	(defined $opt_symblink)  
   and	(-e $opt_symblink)
   and	(!-l $opt_symblink) )	{ 
		die "==> Regular file already exists, cannot create $opt_symblink as Symbolic Link.\n".
		"==> Script abends";
}

if ((defined $opt_hardlink )   and 					# Hard File Link V 4.0
    ($opt_hardlink eq "") ) 		{			    # ignore blank
	undef $opt_hardlink;
}

#if ((defined $opt_hardlink) and 					# V4.1
#    (defined $opt_span)    )	{
#		die "==> Options -span and -hardlink are mutually exclusive - please correct.\n".
#		"==> Script abends";
#}

################################################################################
# Equalize backup actions										V4.2.2
################################################################################
if ($opt_a eq "BACKUP FULL") 	{	$opt_a = "BACKUP" }
if ($opt_a eq "BACKUP DELTA") 	{	$opt_a = "BACKUP INCREMENTAL" }

################################################################################
# Check existence of DB2, write information to console
################################################################################
xmpPerl::chkDB2();
xmpPerl::prtHeaderDB2("$0 - DB2 Backup/Restore - Version $VER");
print "Database and Action requested: $opt_d / $opt_a\n";

if ($opt_a eq "SCRIPT")	{								# V4.2.1
	print "Name of RESTORE script file  : $opt_f\n";
}

################################################################################
# DB Configuration Values
################################################################################
$work = xmpPerl::doCmdOut("db2 get db cfg for $opt_d  | grep 'SQL'");
if ($work =~ "SQL1013N") {
	die "==> This database could not be found. DB2 says ...\n".
	"==> ".xmpPerl::trim($work)."\n".
	"==> Script abends";
}
$work = xmpPerl::doCmdOut("db2 get db cfg for $opt_d  | grep 'Path to log files'");
($work,$dbcfg_logpath)	= split("=",$work);
$dbcfg_logpath 		= xmpPerl::trim($dbcfg_logpath);
$dbcfg_nextlog 		= xmpPerl::getDBCfgValue($opt_d,uc "Next active log file");
$dbcfg_firstlog 	= xmpPerl::getDBCfgValue($opt_d,uc "First active log file");
$dbcfg_logretain 	= xmpPerl::getDBCfgValue($opt_d,"LOGRETAIN");
$dbcfg_userexit 	= xmpPerl::getDBCfgValue($opt_d,"USEREXIT");
$dbcfg_logfilsiz 	= xmpPerl::getDBCfgValue($opt_d,"LOGFILSIZ");
$dbcfg_logprimary 	= xmpPerl::getDBCfgValue($opt_d,"LOGPRIMARY");
#$dbcfg_consistent 	= xmpPerl::getDBCfgValue($opt_d,uc "Database is consistent");
$dbcfg_consistent 	= xmpPerl::getDBConsistent($opt_d);

print "Log Path ....................: $dbcfg_logpath\n";
if (  (defined $dbcfg_nextlog )  
   && ($dbcfg_nextlog ne "")     ) {
	print "Next active Log File ........: $dbcfg_nextlog\n";}
if (  (defined $dbcfg_firstlog ) 
   && ($dbcfg_firstlog ne "")    ) {
	print "First active Log File........: $dbcfg_firstlog\n";}
print "Log retain / User Exit ......: $dbcfg_logretain / $dbcfg_userexit\n";
print "Log Files: No. and Size in 4K: $dbcfg_logprimary * $dbcfg_logfilsiz\n";
print "Database currently consistent: $dbcfg_consistent\n";
 
if (defined $opt_x)	{									# V3.1
	print "Additional Backup Parameters : $opt_x\n";
	$add_options = $opt_x;
}
if (defined $opt_span)	{								# V3.0 Backup Spanning
	print "Spanning Backups ............: $opt_span files\n";
}
#if (   ( (defined $opt_compress)						# V3.0 Compression
#      && ($opt_compress = "YES") )
#	or ( !defined $opt_compress)  ) {					# V4.0 is default now
#	$s_compress = "COMPRESS";
#	print "Built-in Compression ........: YES (deflt.)\n";
#}
print "Built-in Compression ........: YES (deflt.)\n";	# V4.0 as default

if (defined $opt_symblink)	{							# V4.0 Symbolic Link
	print "Create Symbolic Link ........: YES\n";
}

if (defined $opt_hardlink)	{							# V4.0 Hard Link
	print "Create Hard Link ............: YES\n";
}

################################################################################
# Obtain actual Backup Information, list if requested
################################################################################
print "--------------------------------------------------------------------------------\n";
GetEvents();
CheckFiles();
GetBackupGroups();										# V3.0
print "Current Backup Status .......: ".$bg." backup(s) registered in DB2 RHF\n";
print "- restorable Backups ........: ".CountExistingBackupGroups()." registered and file(s) ok\n";
print "  - Full Backups ............: ".CountExistingFullBackupGroups()." registered and file(s) ok\n";
print "- existing Backup Files .....: ".CountExistingBackupFiles()." registered file(s)\n";
print "- missing Backup Files ......: ".CountMissingBackupFiles()." file(s) moved or deleted\n";

################################################################################
# Show existing backup files, registered in RHF or unregistered
################################################################################
#if (defined $opt_f) {									# V4.2
if ( (defined $opt_f) and
     ($opt_a ne "SCRIPT") ) {
	getBackupFiles();
	print "- unregistered Backup Files  : $n_bkp_unregistered\n";
	print "- List of Backup Files ......: in path $opt_f\n";	
	for ($n = 1; $n <= $bf; $n++) {
		if ($bf_rhf{$n}) {
			print "  - ".$bf_basename{$n}." registered in RHF\n";}
		else {	print "  - ".$bf_basename{$n}." not registered\n";
		}
	}
}
else {	print "                               (specify option -f to extend checks!)\n";
}

################################################################################
# Action SCRIPT: Create/check script file
################################################################################
if ( ($opt_a eq "SCRIPT") and
     (defined $opt_f) 		)	{
	if (! open(hFile, "> $opt_f") ) 	{  
		die "$0 cannot write into RESTORE script file $opt_f \n".
		    "abends"; 
	}
	close(hFile); 
	chmod 0775,$opt_f;
}

################################################################################
# Function LIST requested:
################################################################################
if ($opt_a eq "LIST") {
	ListEvents();
}

################################################################################
# Function CHECK requested:
################################################################################
if ($opt_a eq "CHECK") {
	ListEvents();
	############################################################################
	# For V7.x Backups check Backup File integrity 
	############################################################################
	#if (   ($DB2Version =~ "V7")  {				# V3.0
	if (xmpPerl::doCmdOut("which db2ckbkp") !~ "no db2ckbkp in"){	# V3.0
		print "\nChecking Backup Files using db2ckbkp Utility:\n";
		print "============================================\n";
		$f_on = 0;
		$i    = 0;						# V2.3
		########################################################################
		# Process internal Database Event Table
		########################################################################
		for ($n = 1; $n <= $e; $n++) {
                	if (  ($e_oper{$n} eq "B")
			   && ($e_bkp{$n} eq "X") 
			   && ($e_bkpfile{$n} ne "") ) {
				print "- Backup: $e_bkpfile{$n}\n";
				Switch: {
					if ($e_bkpfile{$n} =~ /\.Z/) {
						print "  File is compressed - cannot be checked.\n";
						last Switch;}
					if ($e_bkpfile{$n} =~ /.tar/) { 
						print "  File is an archive - cannot be checked.\n";
						last Switch;}
					if ($e_bkpfile{$n} =~ /.gz/) { 	# V2.4
						print "  File is an archive - cannot be checked.\n";
						last Switch;}
					if ($e_bkpfile{$n} =~ /.zip/) { 
						print "  File is compressed - cannot be checked.\n";
						last Switch;}
					print "Backup Check Utility started : ".xmpPerl::getTimestamp()."\n";
					$dm = xmpPerl::doCmdOut("db2ckbkp -h $e_bkpfile{$n}");
					print "Backup Check Utility ended ..: ".xmpPerl::getTimestamp()."\n";

					if ($dm !~ / successful/) { 
						$f_on = 1;
						print "  File is not OK.\n";
						print "  $dm\n";
					}
					else {	print "  File is OK.\n";
						print "  $dm\n";
						$i++;
					}
				}
			}
		}
		########################################################################
		# Not at least one backup file successfully checked#
		########################################################################
		if ($i eq 0) {
			die "==> Backup Check ended with errors.\n".
			"==> No backup files found or not at least one check\n".
			"==> successfully passed. Script abends";
		}
		########################################################################
		# Backup Check detected errors
		########################################################################
		if ($f_on) {
			die "==> Backup Check ended with errors.\n".
			"==> Backup files found but not at least one check\n".
			"==> successfully passed. Script abends";
		}
		else {	print "Checking all Backup Files successfully finished.\n";}
	}		
	else {
		print "Sorry, cannot find db2ckbkp DB2 Utility in PATH.\n";
	}
}

################################################################################
# New BACKUP requested
################################################################################
if (  ($opt_a eq "BACKUP") 
   or ($opt_a eq "BACKUP INCREMENTAL") )	{
	############################################################################
	# Check Consistency State
	############################################################################
	if ($dbcfg_consistent eq "NO") {
		die "==> $opt_d is not in consitent state - cannot backup database.\n".
		"==> This may happen whenever database is still active and in use.\n".
		"==> Script abends";
	}
	############################################################################
	# Check path's properties
	############################################################################
	if (  (!-d $opt_f) 
	   or (!-r $opt_f)
	   or (!-w $opt_f) ) {
		die "==> Backup path $opt_f is not a directory or not ready for R/W.\n".
		"==> Script abends";
	}
	############################################################################
	# Thread(s) connected to Database?
	############################################################################
	GetThreads();
	$i = $t;
	for ($t = 1; $t<=$i; $t++) {
		$fb = xmpPerl::trim($t_dbname{$t});				# Database Name
		$f8 = xmpPerl::trim($t_pid{$t});				# Coordinator PID
		$f1 = xmpPerl::trim($t_authid{$t});				# Auth ID
		$f2 = xmpPerl::trim($t_applname{$t});			# Application Name
		if ($opt_d eq $fb) {
			die "==> One or more application threads connected to database $opt_d.\n".
			"==> Application $f2 with PID=$f8 under ID=$f1.\n".
			"==> Cannot start creation of Offline Database Backup.\n".
			"==> Script abends";
		}
	}

	############################################################################
	# Remove obsolete Backup Entries, if requested
	############################################################################
	#if (defined $opt_g) {											# V4.0
	if (	(defined $opt_g) 										# V4.0
	   and  ($opt_a ne "BACKUP INCREMENTAL") ) 	{					# V4.0
		########################################################################
		# Determine number of full backups to keep
		########################################################################
		#if ($opt_a eq "BACKUP INCREMENTAL") {
			#$dm = $opt_g;}											# V2.1
		#else {	$dm = ($opt_g -1) };								# V2.1
		########################################################################
		# Check whether files exist and should be removed			V 4.0
		########################################################################
		print "Backup File Housekeeping ....: \n";
		print "- Full backups to keep ......: $opt_g (includes next to create right now)\n";
		$dm = 0;
		for ($n = $bg; $n>=1; $n--)	{					
			####################################################################
			# More Backup groups exist than required
			####################################################################
			if ( $dm >= ($opt_g -1) )	{
				if (  ($bg_bkp{$n} eq "X")
				   or (   ($bg_bkp{$n} ne "X") 
					and ($bg_fc{$n} > 0) 	))	{
					#print ">>> Remove: n=$n bg=$bg n=$bg_name{$n} t=$bg_type{$n} X=$bg_bkp{$n} fc=$bg_fc{$n} ec=$bg_ec{$n}\n";
					RemoveFilesOfBackupGroup($bg_name{$n});
				}
			}		
			####################################################################
			# Count Full Backup Groups
			####################################################################
			if ( ($dm < ($opt_g -1) )	
			   and ($bg_bkp{$n} eq "X")
			   and ($bg_type{$n} eq "F")
			   and ($bg_fc{$n} > 0) 	){
			   	#print ">>> Keep: n=$n bg=$bg n=$bg_name{$n} t=$bg_type{$n} X=$bg_bkp{$n} fc=$bg_fc{$n} ec=$bg_ec{$n}\n";
				$dm++;
			}
		}
		########################################################################
		# Remove unregistered backup files
		########################################################################
		print "- Unregist. file(s) to delete: $n_bkp_unregistered\n";
		for ($n = 1; $n <= $bf; $n++) {
			if ($bf_rhf{$n} == 0 ) {
				print "  - unreg. file to remove ...: ".basename($bf_name{$n})."\n";
				print "                               ".RemoveFile($bf_name{$n})."\n";
			}
		}
	};						
	############################################################################
	# Multiply path if backup file has to span			# V3.0
	############################################################################
	if ( (defined $opt_span) and
	     ($opt_span > 1) 	)	{
		for ($n = 1; $n <= $opt_span; $n++) {
			$span_files .= ",$opt_f";
		}
		$opt_f = substr($span_files,1);				# remove 1st
		#print ">>>Spanned files: $opt_f\n";
	}

	############################################################################
	# Start Backup Command
	############################################################################
	print "--------------------------------------------------------------------------------\n";
	print "Backup Utility started ......: ".xmpPerl::getTimestamp()."\n";
	if ($opt_a eq "BACKUP INCREMENTAL") {
		$work = xmpPerl::doCmdOut("db2 backup db $opt_d INCREMENTAL ".
				"to $opt_f $add_options COMPRESS");
	}
	else {	$work = xmpPerl::doCmdOut("db2 backup db $opt_d ".
				"to $opt_f $add_options COMPRESS");
	}

	$work = xmpPerl::trim($work);
	if (  ($work =~ 'successful')			# Created succesful? 
	   or ($work =~ 'erfolgreich') ) 	{
		#print "back---->$work<----\n";
		#GetEvents();
		#print "Number of current backups ...: ".CountExistingBackups()."\n";
		#ListEvents();
	}		
	else {
		########################################################################
		# Thread(s) connected to Database?			V2.4
		########################################################################
		GetThreads();
		$i = $t;
		for ($t = 1; $t<=$i; $t++) {
			$fb = xmpPerl::trim($t_dbname{$t});	# Database Name
			$f8 = xmpPerl::trim($t_pid{$t});	# Coordinator PID
			$f1 = xmpPerl::trim($t_authid{$t});	# Auth ID
			$f2 = xmpPerl::trim($t_applname{$t});	# Application Name
			if ($opt_d eq $fb) {
				die "==> One or more application threads connected to database $opt_d.\n".
				"==> Application $f2 with PID=$f8 under ID=$f1.\n".
				"==> Cannot start creation of Offline Database Backup.\n".
				"==> Script abends";
			}
		}
		########################################################################
		# Abend script, print SQL error message
		########################################################################
		die "==> Backup failed!\n".
		"==> $work\n".
		"==> Script abends";
	}
	print "Backup Utility ended ........: ".xmpPerl::getTimestamp()."\n";
	############################################################################
	# Success
	############################################################################
	GetEvents();						# V2.0
	CheckFiles();						# V3.0
	GetBackupGroups();					# V3.0

	#print "Backup successfully created  : \n";						# V3.0
	#print "Backup Path (FROM) ..........: $e_path{$e}\n";
	#print "Backup Date (TAKEN AT) ......: $e_start{$e}\n";
	#print "Backup Type .................: $e_type{$e}\n";			# V2.0

	#print "Backup Name .................: $bg_name{$bg}\n";
	print "Backup File Path (FROM) .....: $e_path{$e}\n"; 		# $bg_path{$bg}\n";
	print "Backup Date (TAKEN AT) ......: $bg_start{$bg}\n";
	print "Backup Type .................: $bg_type{$bg} (F=full, I=incremental)\n";	
	print "Backup Parts ................: $bg_ec{$bg} (>1= spanned files)\n";	
	print "Backup Files assoc. to Parts : $bg_ec{$bg} (registered and existing)\n";		

	ListAndLinkFilesOfBackupGroup($bg_name{$bg});					# V4.1
	
	############################################################################
	# List Backup and File Status
	############################################################################	
	ListEvents();

	print "Current Backup Status .......: ".$bg." backup(s) registered\n";
	print "- restorable Backups ........: ".CountExistingBackupGroups()." registered, file(s) ok\n";
	print "  - Full Backups ............: ".CountExistingFullBackupGroups()." registered, file(s) ok\n";
	print "- existing Backup Files .....: ".CountExistingBackupFiles()." registered file(s)\n";
	print "- missing Backup Files ......: ".CountMissingBackupFiles()." file(s) moved or deleted\n";

	############################################################################
	# Write this Event to DBA Log File
	############################################################################
	xmpPerl::LogLine("DBA I ".$0." $opt_d $opt_a $e_start{$e}");	# V 3.0.1
}

################################################################################
# RESTORE Database Backup requested
################################################################################
if ($opt_a eq "RESTORE") {
	if (! defined $opt_g) {
		$opt_g = 0;				# Lastest Backup File
	}
	############################################################################
	# Backup File(s) exist(s)?
	############################################################################
	#$n = CountExistingBackups();
	$n = CountExistingBackupGroups();			# V3.0
	if ( $n == 0) {
		die "==> $opt_d cannot be restored - no backup file(s) exist(s)!\n".
		"==> Script abends";
	}

	if (  (($opt_g * -1)+1) > $n )	{
		die "==> $opt_d cannot be restored to Backup File generation $opt_g\n".
		"==> Only $n Backup Group(s) exist(s). Please correct generation number!\n".
		"==> Script abends";
	}
	############################################################################
	# Backup File(s) exist(s)?				# V2.0
	############################################################################
	$n = CountExistingFullBackupGroups();			# V3.0
	if ( $n == 0) {
		die "==> $opt_d cannot be restored - no full backup exist!\n".
		"==> Script abends";
	}
	############################################################################
	# Thread(s) connected to Database?
	############################################################################
	GetThreads();
	$i = $t;
	for ($t = 1; $t<=$i; $t++) {
		$fb = xmpPerl::trim($t_dbname{$t});	# Database Name
		$f8 = xmpPerl::trim($t_pid{$t});	# Coordinator PID
		$f1 = xmpPerl::trim($t_authid{$t});	# Auth ID
		$f2 = xmpPerl::trim($t_applname{$t});	# Application Name
		if ($opt_d eq $fb) {
			die "==> One or more application threads connected to database $opt_d.\n".
			"==> Application $f2 with PID=$f8 under ID=$f1.\n".
			"==> Cannot start Restore Database Backup.\n".
			"==> Script abends";
		}
	}
	############################################################################
	# Get and print Information about Backup and Restore Events
	############################################################################
	ListEvents();
	print "Backup group to be restored  : $opt_g (where 0=lastest, -1=one before ...)\n";

	############################################################################
	# Determine corresponding Backup Event
	############################################################################
	$dm = ($opt_g * -1);				# transform to positive
	#for ($n = $e; $n > 0; $n--) {			# V3.0
	for ($n = $bg; $n > 0; $n--) {
		#if (   ($e_oper{$n} eq "B")		# V3.0
		if  (  ($bg_bkp{$n}  eq "X") 
		   and ($dm         >=  0  ) ) {
			$dm--;
			$i = $n;			
		};					# if
	}; 						# for
	$n = $i;					# Event requested!!!!

	############################################################################
	# Print Information about Backup File 
	############################################################################
	#print "Backup File Name ............: $e_bkpfile{$n}\n";
	#print "Backup Path (FROM) ..........: $e_path{$n}\n";
	#print "Backup Date (TAKEN AT) ......: $e_start{$n}\n";
	#print "Backup Type .................: $e_type{$n}\n";	# V2.0
	print "Backup Name .................: $bg_name{$n}\n";
	print "Backup File Path (FROM) .....: $bg_path{$n}\n";
	print "Backup Date (TAKEN AT) ......: $bg_start{$n}\n";
	print "Backup Type .................: $bg_type{$n} (F=full, I=incremental)\n";	
	print "Backup Parts ................: $bg_ec{$n} (>1= spanned files)\n";	
	print "Backup Files assoc. to Parts : $bg_ec{$n} (registered and existing)\n";	

	#$ino = stat($e_bkpfile{$n});
	#print "Backup File Owner/Group .....: ".getpwuid($ino->uid)."/".getgrgid($ino->gid)."\n";
	#print "Backup File Size ............: ".(($ino->size)/1024)." KB\n";

	############################################################################
	# Start Restore Command
	############################################################################
	print "--------------------------------------------------------------------------------\n";
	print "Restore Utility started .....: ".xmpPerl::getTimestamp()."\n";

	if ($bg_type{$n} eq "I" ) {				# V2.0
		$work = xmpPerl::doCmdOut("db2 RESTORE DB $opt_d ".
				"INCREMENTAL AUTOMATIC FROM $bg_path{$n} ".
				"TAKEN AT $bg_start{$n} ".
				"$add_options WITHOUT PROMPTING");
	}
	else {	$work = xmpPerl::doCmdOut("db2 RESTORE DB $opt_d FROM $bg_path{$n} ".
				"TAKEN AT $bg_start{$n} ".
				"$add_options WITHOUT PROMPTING");
	}
	$work = xmpPerl::trim($work);
	if (  ($work =~ 'successful')			# Succesfully restored?
	   or ($work =~ 'erfolgreich') ) 	{
		#print "restore---->$work<----\n";
		print "Current Restore Status ......: Database successfully restored!\n";
	}		
	else {
		die "==> Restore failed!\n".
		"==> $work\n".
		"==> Script abends";
	}
	print "Restore Utility ended .......: ".xmpPerl::getTimestamp()."\n";

	########################################################################
	# Write this Event to DBA Log File
	########################################################################
	xmpPerl::LogLine("DBA I ".$0." $opt_d $opt_a $bg_name{$n}");

}

################################################################################
# SCRIPT: Creates DB2 Database Redirected Restore Script				V4.2
################################################################################
if ($opt_a eq "SCRIPT") {
	############################################################################
	# This works only for DB2 V9.1
	############################################################################
	if (substr($DB2Version,0,1) ne "9") 	{
		die "==> Option $opt_a works only with DB2 V9.x or later version!\n".
		    "==> Current version is $DB2Version.\n".
		    "==> Script abends";
	}
	############################################################################
	# Generration Number specified
	############################################################################
	if (! defined $opt_g) {
		$opt_g = 0;				# Lastest Backup File
	}
	############################################################################
	# Backup File(s) exist(s)?
	############################################################################
	$n = CountExistingBackupGroups();
	if ( $n == 0) {
		die "==> Cannot create restore script for $opt_d - no backup file(s) exist(s)!\n".
		"==> Script abends";
	}

	if (  (($opt_g * -1)+1) > $n )	{
		die "==> Cannot create restore script for $opt_d to Backup File generation $opt_g\n".
		"==> Only $n Backup Group(s) exist(s). Please correct generation number!\n".
		"==> Script abends";
	}
	############################################################################
	# Backup File(s) exist(s)?			
	############################################################################
	$n = CountExistingFullBackupGroups();
	if ( $n == 0) {
		die "==> Cannot create restore script for $opt_d - no full backup exist!\n".
		"==> Script abends";
	}
	############################################################################
	# Get and print Information about Backup and Restore Events
	############################################################################
	ListEvents();
	print "Backup group to be restored  : $opt_g (where 0=lastest, -1=one before ...)\n";

	############################################################################
	# Determine corresponding Backup Event
	############################################################################
	$dm = ($opt_g * -1);				# transform to positive
	#for ($n = $e; $n > 0; $n--) {		
	for ($n = $bg; $n > 0; $n--) {
		#if (   ($e_oper{$n} eq "B")		
		if  (  ($bg_bkp{$n}  eq "X") 
		   and ($dm         >=  0  ) ) {
			$dm--;
			$i = $n;			
		};					# if
	}; 						# for
	$n = $i;				# Event requested!!!!

	############################################################################
	# Print Information about Backup File 
	############################################################################
	print "Backup Name .................: $bg_name{$n}\n";
	print "Backup File Path (FROM) .....: $bg_path{$n}\n";
	print "Backup Date (TAKEN AT) ......: $bg_start{$n}\n";
	print "Backup Type .................: $bg_type{$n} (F=full, I=incremental)\n";	
	print "Backup Parts ................: $bg_ec{$n} (>1= spanned files)\n";	
	print "Backup Files assoc. to Parts : $bg_ec{$n} (registered and existing)\n";	

	############################################################################
	# Start Restore Script file generation 
	############################################################################
	print "--------------------------------------------------------------------------------\n";
	print "Start creation of script file: ".xmpPerl::getTimestamp()."\n";	

	$work = xmpPerl::doCmdOut("db2 RESTORE DB $opt_d FROM $bg_path{$n} ".
			"TAKEN AT $bg_start{$n} ".
			"REDIRECT GENERATE SCRIPT $opt_f");
	$work = xmpPerl::trim($work);
	if (  ($work =~ 'successful')			# Succesfully created?
	   or ($work =~ 'erfolgreich') ) 	{
		#print "restore---->$work<----\n";
		print "Current Creation Status .....: Script file $opt_f\n";
		print "                               successfully created!\n";
	}		
	else {
		die "==> Script file $opt_f creation failed!\n".
		"==> $work\n".
		"==> Script abends";
	}
	print "End creation of script file  : ".xmpPerl::getTimestamp()."\n";	

}

print "***End***\n";

exit 0;

################################################################################
# GetEvents: Get Events in Database History, i.e. Backups
################################################################################
sub GetEvents {
	############################################################################
	# DBI Switches 
	############################################################################
	my $switch = DBI->internal;
	$switch->debug(0);                     # 1=normal debug, 2=detailed handle trace
	#print "DBI Version and Attributes ..: $switch->{'Attribution'}, $switch->{'Version'}\n";
	#print "DBI Drivers .................: ",join(", ",DBI->available_drivers()),"\n";

	############################################################################
	# Connect to DWH Source Database holding Delta Processing Control Table
	############################################################################
	$dbh = DBI->connect("dbi:DB2:$opt_d", 
						$opt_u, 
						$opt_p,
						{ PrintError => 0, # don't print error by default
						RaiseError => 0, # don't print error and die by default
						AutoCommit => 0, # don't commit after each statement
						ChopBlanks => 0, # don't remove trailing blanks on CHAR-Types
						} )
			|| die ">>>$0 Problem mit Connect to $opt_d\n>>>".$DBI::errstr;
	############################################################################
	# Access DB2 Table Function ADMIN_LIST_HIST()						V4.0
	############################################################################
    $stmt = "SELECT OPERATION, OPERATIONTYPE, OBJECTTYPE,
				START_TIME, END_TIME,
				FIRSTLOG, LASTLOG,
                START_TIME||RIGHT(DIGITS(SEQNUM),3) AS BACKUP_ID,
				COALESCE(COMMENT,' '),
				COALESCE(LOCATION,' '), COALESCE(DEVICETYPE,' ')
			FROM   TABLE (ADMIN_LIST_HIST()) X 
			WHERE  SQLCODE IS NULL
			AND    OPERATION IN ('B','R','F')
			ORDER  BY START_TIME";
	############################################################################
	# Prepare and Execute DB2 statement									V4.0
	############################################################################
	$sth = $dbh->prepare($stmt) || die "Statement #1a: $stmt\n".$dbh->errstr;
	$sth->execute() || die "Statement #1b: $stmt\n".$dbh->errstr;
	############################################################################
	# Read Result Set into internal table								V4.0
	############################################################################
	$e=1;
	while ( ((	$e_oper{$e},
				$e_type{$e},
				$e_obj{$e},
				$e_start{$e},	  
				$e_end{$e}, 
				$e_1log{$e},
				$e_clog{$e},
				$e_ts{$e},
				$e_comm{$e},
				$e_path{$e}, 
				$e_dev{$e}	) = $sth->fetchrow()) )
	{  	#print "- ENTRY $e: $e_oper{$e} with path $e_path{$e} and timestamp/seq $e_ts{$e}\n";
		$e_bkp{$e} 		= "";
		$e_bkpfile{$e} 	= "";
		$e++;
	}
	############################################################################
	# Did an error occur?
	############################################################################
	if ( $dbh->err ) 	{
		die "$0 Statement #1c: $stmt \n".
		"abended with $dbh->errstr";
	}
	$e--; 		
	############################################################################
	# Disconnect from DWH Target Database
	############################################################################
	$sth->finish()   || die $dbh->errstr;
	$dbh->commit     || die $dbh->errstr;
	$dbh->disconnect || die $dbh->errstr;	
	sleep 2;										# wait to be disconnected
}

################################################################################
# CheckFiles: Process Event Table, check existence of Backup Files	V 3.0
################################################################################
sub CheckFiles {
   my ($n,$i) 	= 0;
   my $dm 	= "";

	############################################################################
	# Check existence of Files if entry represents a Backup Process
	############################################################################
	#print "NUMBER OF ENTRIES ....: $e\n";
	for ($n = 1; $n <= $e; $n++) {
		########################################################################
		# Backup Entry?
		########################################################################
		$e_bkpfile{$n} 	= "";
		$e_bkp{$n}	= "";
		#print "- ENTRY $n: $e_oper{$n} with path $e_path{$n} and timestamp/seq $e_ts{$n}\n";
			if (  ($e_oper{$n} eq "B")	 							# Backup
	           && ($e_path{$n} ne "")  )     {						# Path
			#print "- ENTRY IS A BACKUP, PATH EXISTS ...\n";
	   		####################################################################
			# Backup File w/o suffix
			####################################################################
			$dm = "$e_path{$n}/$opt_d.*.".
			      substr($e_ts{$n},0,14).".".substr($e_ts{$n},14);
			#print "search: $dm\n";
			@work = glob($dm);
			$e_bkpfile{$n} = $work[0];
			if (    (defined $e_bkpfile{$n})						# V3.1
			    and ($e_bkpfile{$n} eq "fault")) {
				$e_bkpfile{$n} = "";
			}
			#$e_bkpfile{$n} = glob($dm);
			#print "result: ($?)==>$e_bkpfile{$n}<==\n";
			if ((defined $e_bkpfile{$n}) and 						# Files found?
			    ($e_bkpfile{$n} ne "") ) 	{	
				$e_bkp{$n} = "X";
				#print "result: ".$e_bkpfile{$n}."=X\n";
			}
			else {
		   		################################################################
				# Backup File w/ suffix (e.g. compressed file .Z)
				################################################################
				$dm = "$e_path{$n}/$opt_d.*.".
				      substr($e_ts{$n},0,14).".".substr($e_ts{$n},14).
				      ".*";
				#print "next search: $dm\n";
				@work = glob($dm);
				$e_bkpfile{$n} = $work[0];
				if (    (defined $e_bkpfile{$n})					# V3.1
				    and ($e_bkpfile{$n} eq "fault")) {
					$e_bkpfile{$n} = "";
				}
				#$e_bkpfile{$n}	= glob($dm);
				#print "next result: $e_bkpfile{$n}\n";
				if ((defined $e_bkpfile{$n}) and 					# Files found?
				    ($e_bkpfile{$n} ne "") ) 	{
					$e_bkp{$n} = "X";
					#print "next result: ".$e_bkpfile{$n}."=X\n";
				}
			}
		}
	}
}      

################################################################################
# ListEvents: List Events in Database History 
################################################################################
sub ListEvents {
	my ($n,$i) = 0;
	############################################################################
	# Process internal Database Event Table
	############################################################################
	for ($n = 1; $n <= $e; $n++) {
		$c1 = $e_oper{$n};
		$c2 = $e_obj{$n};
		$c3 = $e_ts{$n};
		if (!$c3) {$c3=""}								# V4.3.1
		$c4 = $e_type{$n};
		$c5 = $e_dev{$n};
		$c6 = $e_1log{$n};
		if (!$c6) {$c6=""}								# V4.3.1
		$c7 = $e_clog{$n};
		if (!$c7) {$c7=""}								# V4.3.1
		$cb = $e_path{$n};
		$cd = $e_bkp{$n};
		$c9 = $e_start{$n};
		$ca = $e_end{$n};
		$cc = $e_comm{$n};
		########################################################################
		# Suppress writing entries under circumstances 
		########################################################################
		if ($opt_a eq "LIST") {write}
		else {	if (($e_oper{$n} eq "B") or
			    ($e_oper{$n} eq "R")   ) {write} }
	}
	print 	"--------------------------------------------------------------------------------\n";
}      

################################################################################
# GetThreads: Alle oder nur best. DB-Threads ermitteln und Daten intern speichern
#             (see Code in script xmpTerm.pl !!!)
################################################################################
sub GetThreads {
   my $work;
   my $work2;
   my $startpos = 9;					# Dflt. Start

	$t = 0;

	############################################################################
    # Starting position in DB2 command output is version-dependent!
	############################################################################
	if ($DB2Version =~ "5") {$startpos=9}		# Version 6  17.07.2001		
	if ($DB2Version =~ "6") {$startpos=9}		# Version 6  17.07.2001		
	if ($DB2Version =~ "7") {$startpos=31}		# Version 7  17.07.2001
	if ($DB2Version =~ "8") {$startpos=31}		# Version 8  13.01.2004
	if ($DB2Version =~ "9") {$startpos=31}		# Version 9  V4.2	

	############################################################################
    # DB2 List Applications show detail
	############################################################################
	$work = xmpPerl::doCmdOut("db2 list applications show detail");
	undef $/;
	@work = split("\n",$work);
	#print "$#work lines returned from DB2 List Applications Command.\n";

	if ($#work == 0) {return}

	$f_on = 0;

	foreach $work (@work) {

		#($f1,$f2,$f3,$f4,$f5,$f6,$f7,$f7,$f9,$fa,$fb,$fc) = split(" ",$work);
		if (length($work) > 0) {
			####################################################################
			# Diese folgenden Bedingungen sind Versions-abhängig!    
			####################################################################
			$f1 = substr($work,0,8);					# Authid
			$f2 = substr($work,$startpos,20);			# Appl. Name
			$f3 = substr($work,$startpos+21,10);		# Appl. Handle
			$f4 = substr($work,$startpos+32,30);		# Appl. ID
			$f5 = substr($work,$startpos+63,4);			# Seq. No
			$f6 = substr($work,$startpos+68,10);		# No Agents.
			if ($DB2Version =~ "9")		{				# 28.12.2006
				$f4 = substr($work,$startpos+32,62);	# Appl. ID			
				$f5 = substr($work,$startpos+95,5);		# Seq. No
				$f6 = substr($work,$startpos+101,10);	# No Agents.
				$f7 = substr($work,$startpos+112,16);	# Coord. Node
				$f8 = substr($work,$startpos+129,15);	# Coord PID/Thread
				if (length($work) >= $startpos+145) {
					$f9 = substr($work,$startpos+145,30)}	# Status
				else {	$f9 = "n/a" }
				if (length($work) >= $startpos+176) {			
					$fa = substr($work,$startpos+176,26)}	# Status Change	
				else {	$fa = "n/a" }
				if (length($work) >= $startpos+203) {
					$fb = substr($work,$startpos+203,8)}	# DBNAME
				else {	$fb = "n/a" }
				if (length($work) >= $startpos+212) {
					$fc = substr($work,$startpos+212)}	# DB Path
				else {	$fc = "n/a" }
			}
			elsif ($DB2Version =~ "8")		{			# 13.01.2004
				$f7 = substr($work,$startpos+79,16);	# Coord. Node
				$f8 = substr($work,$startpos+92,15);	# Coord PID/Thread
				if (length($work) >= $startpos+112) {
					$f9 = substr($work,$startpos+112,30)}	# Status
				else {	$f9 = "n/a" }
				if (length($work) >= $startpos+143) {			
					$fa = substr($work,$startpos+143,26)}	# Status Change	
				else {	$fa = "n/a" }
				if (length($work) >= $startpos+170) {
					$fb = substr($work,$startpos+170,8)}	# DBNAME
				else {	$fb = "n/a" }
				if (length($work) >= $startpos+179) {
					$fc = substr($work,$startpos+179)}	# DB Path
				else {	$fc = "n/a" }
			}			
			else 	{
				$f7 = substr($work,$startpos+79,12);	# Coord. Node
				$f8 = substr($work,$startpos+92,15);	# Coord PID/Thread
				if (length($work) >= $startpos+108) {
					$f9 = substr($work,$startpos+108,30)}	# Status
				else {	$f9 = "n/a" }
				if (length($work) >= $startpos+139) {			
					$fa = substr($work,$startpos+139,26)}	# Status Change
				else {	$fa = "n/a" }
				if (length($work) >= $startpos+166) {
					$fb = substr($work,$startpos+166,8)}	# DBNAME
				else {	$fb = "n/a" }
				if (length($work) >= $startpos+175) {
					$fc = substr($work,$startpos+175)}	# DB Path
				else {	$fc = "n/a" }
			}
		}

		#print length($work)."$f1 $f2 $f3\n";

		########################################################################
		# Wenn Ausgabedaten lokalisiert werden konnten
		########################################################################
		if ($f_on) {
			#print "eingetragen\n";
			$t++;
			$t_authid{$t} 	= $f1;			# Auth ID
			$t_applname{$t} = $f2;			# Application Name
			$t_id{$t} 	= xmpPerl::trim($f3);	# Thread ID
			$t_applid{$t} 	= $f4;			# Application ID
			$t_seq{$t} 	= $f5;				# Sequence
			$t_agentno{$t}	= $f6;			# Number of Agents
			$t_node{$t} 	= $f7;			# Coordinator Node
			$t_pid{$t} 	= xmpPerl::trim($f8);	# Coordinator PID
			$t_status{$t} 	= $f9;			# Status
			$t_statchgtm{$t}= $fa;			# Status Change Time
			$t_dbname{$t} 	= xmpPerl::trim($fb);	# Database Name
			$t_path{$t} 	= $fc;			# DB Path
		}

		########################################################################
		# Jetzt gehts mit echten Daten los
		########################################################################
		if ($f1 eq "--------") {$f_on = 1}
	}		
}


################################################################################
# getBackupFiles: Provides files matching a search pattern 
################################################################################
sub getBackupFiles {
   my ($n,$i) = 0;

	$n_bkp_unregistered = 0;
	$DB2INSTANCE = xmpPerl::trim($ENV{DB2INSTANCE});
	#$work 	= xmpPerl::doCmdOut("ls $opt_f/$opt_d.*.$DB2INSTANCE.NODE*.*.*.*"); 		# V2.1.1
	$work 	= xmpPerl::doCmdOut("ls $opt_f/$opt_d.*.$DB2INSTANCE.NODE*.*.*.* 2>/dev/null");	# V2.1.1
	undef $/;
	@work 	= split("\n",$work);
	$bf	= 0;

	foreach $work (@work) {
		#print "==>Bkp File: $work\n";
		$bf++;
		$bf_name{$bf}     = xmpPerl::trim($work);
		$bf_basename{$bf} = basename(xmpPerl::trim($work));
		$bf_rhf{$bf}      = 0;		
	}

	############################################################################
	# Compare Backup File Table with RHF Event Table. File registered?
	############################################################################
	for ($n = 1; $n <= $e; $n++) {
		if ($e_oper{$n} eq "B" & defined $e_bkpfile{$n}) {		# backup event
			for ($i = 1; $i <= $bf; $i++) {
				################################################################
				# $opt_f contains "." which is the current path			V 4.0
				################################################################
				#print "==> substr($bf_name{$i},0,1)=".substr($bf_name{$i},0,1)."\n";
				if (substr($bf_name{$i},0,1) eq ".") 	{
					$bf_name{$i} = getcwd.substr($bf_name{$i},1);	# Currdir
				}				
				#print $e_bkpfile{$n}." -- ".$bf_name{$i}."\n";				
				################################################################
				# existing backup file registered as file in recovery history?	
				################################################################				
				if ( $e_bkpfile{$n} eq $bf_name{$i} ) {	
					$bf_rhf{$i} = 1;						# registered in RHF
					#print "  registered!\n";
				}

			}
		}
	}
	############################################################################
	# Count files which exist but aren't registered in RHF Event Table
	############################################################################
	for ($i = 1; $i <= $bf; $i++) {
		if ( $bf_rhf{$i} eq 0) {
			$n_bkp_unregistered++;
	 	}
	}
}      

################################################################################
# CountExistingBackupFiles: Provides number of existing backup files
################################################################################
sub CountExistingBackupFiles {
   my ($n,$i) = 0;
   my $work = 0;						# V2.0
	############################################################################
	# Process internal Database Event Table
	############################################################################
	for ($n = 1; $n <= $e; $n++) {
                if ($e_bkp{$n} eq "X") {
			$work++;
		}
	}
	return ($work);				
}      

################################################################################
# CountExistingBackupGroups: Provides no. of bkps w/ existing backup files V3.0
################################################################################
sub CountExistingBackupGroups {
   my ($n,$i) = 0;
   my $work = 0;				
	############################################################################
	# Process internal Backup Group Table
	############################################################################
	for ($n = 1; $n <= $bg; $n++) {
                if ($bg_bkp{$n} eq "X") {$work++;}
	}
	return ($work);		
}      

################################################################################
# CountMissingBackupFiles: Provides number of missing backup files	# V2.0
################################################################################
sub CountMissingBackupFiles {
   my $work = 0;
	############################################################################
	# Process internal Database Event Table				# V2.0
	############################################################################
	for ($n = 1; $n <= $e; $n++) {
                if ($e_bkp{$n} eq "") {
			$work++;				# Missing Backup Files
		}
	}
	return ($work);						# Return number
}      

################################################################################
# CountExistingFullBackupGroups: Provides no. of existing full backups	# V3.0
################################################################################
sub CountExistingFullBackupGroups {
   my $work = 0;

	############################################################################
	# Process internal Backup Group Table
	############################################################################
	for ($n = 1; $n <= $bg; $n++) {
                if (   ($bg_bkp{$n} eq "X") 
    		   and ($bg_type{$n} eq "F") ) {$work++}
	}
	return ($work);		
}      

################################################################################
# RemoveFilesOfBackupGroup: Deletes Backup Files		 			# V3.0
################################################################################
sub RemoveFilesOfBackupGroup {
   my ($n,$i) = 0;
   my $del_bg 	= "";

	($del_bg) = @_;
	############################################################################
	# Process internal Event Table, remove obsolete files
	############################################################################
	#print "RemoveFilesOfBackupGroup: $del_bg\n";
	#print "e=$e\n";
	for ($n = 1; $n <= $e; $n++) {
		if (   (substr($e_ts{$n},0,14) eq $del_bg)
		   and ($e_oper{$n} eq "B")
		   and ($e_bkp{$n}  eq "X")  ) {
				print "- Remove backup file type=".$e_type{$n}."  : ".basename($e_bkpfile{$n})."\n";	
				print "                               ".RemoveFile($e_bkpfile{$n})."\n";
		}
	}
}

################################################################################
# GetBackupGroups: Provides a table of distinct backups groups 	# V3.0
################################################################################
sub GetBackupGroups {
   my ($n,$i) = 0;
   my $found;
	############################################################################
	# Process internal Database Event Table				
	############################################################################
	$bg 	= 0;
	%bg_name= ();					# backup group name
	%bg_type= ();					# backup group type
	%bg_bkp	= ();					# backup group files exist
	%bg_fc	= ();					# backup group exist. files count
	%bg_ec	= ();					# backup group event count

	for ($n = 1; $n <= $e; $n++) {
		$found = 0;
		########################################################################
		# Backup Event already found as part of backup group?
		########################################################################
		for ($i = 1; $i <= $bg; $i++) {
			if (   (substr($e_ts{$n},0,14) eq $bg_name{$i})
			   and ($e_type{$n} eq $bg_type{$i})	)	{
				################################################################
				# Event found in group
				################################################################
				$found = 1;
				$bg_ec{$i}++;			# Event Count
				if ($e_bkp{$n} eq " " )		{
					$bg_bkp{$bg} = " "	}
				if ($e_bkp{$n} eq "X") 		{
					$bg_fc{$bg}++		}
				$bg_path{$bg} = $bg_path{$bg}.",".$e_path{$n};
				#print ">>> Backup Group B: $bg_name{$bg} T=$bg_type{$bg} X=$bg_bkp{$bg} EC=$bg_ec{$bg} FC=$bg_fc{$bg}\n";
			}
		}
		########################################################################
		# Add Backup Event as new backup group
		########################################################################
		if (! $found) {
			$bg++;
			$bg_name{$bg} 	= substr($e_ts{$n},0,14);
			$bg_type{$bg} 	= $e_type{$n};	# Backup Type  
			$bg_start{$bg} 	= $e_start{$n};	# Start Time
			$bg_path{$bg} 	= $e_path{$n};	# Bkp file path
			$bg_ec{$bg} 	= 1;		# Event Count
			$bg_bkp{$bg} 	= $e_bkp{$n};
			if ($e_bkp{$n} eq "X"){
				$bg_fc{$bg} = 1	}	# Existing File Count
			else {	$bg_fc{$bg} = 0	}
			#print ">>> Backup Group A: $bg_name{$bg} T=$bg_type{$bg} X=$bg_bkp{$bg} EC=$bg_ec{$bg} FC=$bg_fc{$bg}\n";
		}
	}
}  
################################################################################
# ListAndLinkFilesOfBackupGroup: List Information of Backup Files		V 4.1
#                                and create Hard or Symbolic Links
################################################################################
sub ListAndLinkFilesOfBackupGroup {
   my ($n,$i) = 0;
   my $src_bg 	= "";
   my $ln = "";

	($src_bg) = @_;
	############################################################################
	# Process internal Event Table, remove obsolete files				V 4.1
	############################################################################
	#print "ListAndLinkFilesOfBackupGroup: $src_bg, Entries: e=$e\n";
	for ($n = 1; $n <= $e; $n++) {
		if (   (substr($e_ts{$n},0,14) eq $src_bg)
		   and ($e_oper{$n} eq "B")
		   and ($e_bkp{$n}  eq "X")  ) {
				$ino = stat($e_bkpfile{$n});			
				print "- Backup File created .......: ".basename($e_bkpfile{$n})."\n";
				print "  - File Size, Owner/Group ..: ";
				if ($ino->size >= 1073741824) {
					print xmpPerl::trim(sprintf("%7.2f",(($ino->size)/1073741824)))." GB, ";
				}
				else	{
					print xmpPerl::trim(sprintf("%7.2f",(($ino->size)/1048576)))." MB, ";
				}	
				print getpwuid($ino->uid)."/".getgrgid($ino->gid)."\n";
				################################################################
				# Create new Symbolic Link								V 4.0
				################################################################
				if (defined $opt_symblink) 	{	
					$ln = $e_bkpfile{$n}."-".$opt_symblink;
					print "  - Symbolic Link created ...: ".basename($ln)."\n";
					if (-l $opt_symblink) {
						print "                               ".RemoveFile($ln)."\n";
					}
					symlink($e_bkpfile{$n},$ln);
					print "                               (link created with RC=$?)\n";		
				}
				################################################################
				# Create new Hard Link									V 4.0
				################################################################
				if (defined $opt_hardlink) 	{	
					$ln = $e_bkpfile{$n}."-".$opt_hardlink;				
					print "  - Hard Link created .......: ".basename($ln)."\n";
					if (-f $opt_hardlink) {
						print "                               ".RemoveFile($ln)."\n";
					}
					if (link $e_bkpfile{$n},$ln) 	{
							print "                               (link created with RC=$?)\n";		
					}
					else	{
							print "                               (Creating link failed with $ERRNO $OS_ERROR)\n";				
					}
				}					
		}
	}
}    

################################################################################
# RemoveFile: Removes/Deletes/Unlinks File, Hardlink or Symbolic Link 	V 4.0
################################################################################
sub RemoveFile {
  my $file = $_[0];
	############################################################################
	# Process internal Database Event Table				
	############################################################################
	if (unlink $file)	{
		return "(file/link removed RC=$?)";
	}
	else	{
		return "(file/link not removed, RC=$?=$!)";
	}
}

################################################################################
########################################################## Sub Routines ########
# SaySyntax ####################################################################
sub SaySyntax {
	die "Usage: [perl] $0 -d db [-a action -f file -g n -x ... -span n -{symb|hard}link id ]\n".
		"	-d database \n".
		"	-a Action: LIST|CHECK|BACKUP [FULL|DELTA|INCREMENTAL]|RESTORE|SCRIPT \n".
		"	   LIST = list an database events, especially backup information \n".				
		"	   CHECK = checks existing backup files using an DB2 tool \n".				
		"	   BACKUP {FULL} = full offline database backup \n".	
		"	   BACKUP {DELTA|INCREMENTAL} = offline incremental database backup \n".		
		"	   RESTORE = restore db using generation n to speficy point-in-time \n".				
		"	   SCRIPT = create restore script using a specifig backup generation \n".
		"	-u authorized user (or environment variable)\n".
		"	-p authorized user password (or enviroment variable) \n".		
		"	For BACKUP (OFFLINE only!):\n".
		"	-f path to backup file\n".
        "	-g n, where n=number of backups to keep (>=1)\n".
        "	-span n, where n=number of backup files (>=1)\n".
        "	-symblink id, creates symbolic link on backup file+id\n".		
        "	-hardlink id, creates hard link on backup file+id\n".		
		"	For RESTORE:\n".
		"	-g backup file generation (0=latest, -1=...)\n".
		"	-x additional backup options\n".
		"      for SCRIPT:\n".
		"	-f name of DB2 Redirected Restore Script to generate\n".
		"abends";
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

474

475

476

477

478

479

480

481

482

483

484

485

486

487

488

489

490

491

492

493

494

495

496

497

498

499

500

501

502

503

504

505

506

507

508

509

510

511

512

513

514

515

516

517

518

519

520

521

522

523

524

525

526

527

528

529

530

531

532

533

534

535

536

537

538

539

540

541

542

543

544

545

546

547

548

549

550

551

552

553

554

555

556

557

558

559

560

561

562

563

564

565

566

567

568

569

570

571

572

573

574

575

576

577

578

579

580

581

582

583

584

585

586

587

588

589

590

591

592

593

594

595

596

597

598

599

600

601

602

603

604

605

606

607

608

609

610

611

612

613

614

615

616

617

618

619

620

621

622

623

624

625

626

627

628

629

630

631

632

633

634

635

636

637

638

639

640

641

642

643

644

645

646

647

648

649

650

651

652

653

654

655

656

657

658

659

660

661

662

663

664

665

666

667

668

669

670

671

672

673

674

675

676

677

678

679

680

681

682

683

684

685

686

687

688

689

690

691

692

693

694

695

696

697

698

699

700

701

702

703

704

705

706

707

708

709

710

711

712

713

714

715

716

717

718

719

720

721

722

723

724

725

726

727

728

729

730

731

732

733

734

735

736

737

738

739

740

741

742

743

744

745

746

747

748

749

750

751

752

753

754

755

756

757

758

759

760

761

762

763

764

765

766

767

768

769

770

771

772

773

774

775

776

777

778

779

780

781

782

783

784

785

786

787

788

789

790

791

792

793

794

795

796

797

798

799

800

801

802

803

804

805

806

807

808

809

810

811

812

813

814

815

816

817

818

819

820

821

822

823

824

825

826

827

828

829

830

831

832

833

834

835

836

837

838

839

840

841

842

843

844

845

846

847

848

849

850

851

852

853

854

855

856

857

858

859

860

861

862

863

864

865

866

867

868

869

870

871

872

873

874

875

876

877

878

879

880

881

882

883

884

885

886

887

888

889

890

891

892

893

894

895

896

897

898

899

900

901

902

903

904

905

906

907

908

909

910

911

912

913

914

915

916

917

918

919

920

921

922

923

924

925

926

927

928

929

930

931

932

933

934

935

936

937

938

939

940

941

942

943

944

945

946

947

948

949

950

951

952

953

954

955

956

957

958

959

960

961

962

963

964

965

966

967

968

969

970

971

972

973

974

975

976

977

978

979

980

981

982

983

984

985

986

987

988

989

990

991

992

993

994

995

996

997

998

999

1000

1001

1002

1003

1004

1005

1006

1007

1008

1009

1010

1011

1012

1013

1014

1015

1016

1017

1018

1019

1020

1021

1022

1023

1024

1025

1026

1027

1028

1029

1030

1031

1032

1033

1034

1035

1036

1037

1038

1039

1040

1041

1042

1043

1044

1045

1046

1047

1048

1049

1050

1051

1052

1053

1054

1055

1056

1057

1058

1059

1060

1061

1062

1063

1064

1065

1066

1067

1068

1069

1070

1071

1072

1073

1074

1075

1076

1077

1078

1079

1080

1081

1082

1083

1084

1085

1086

1087

1088

1089

1090

1091

1092

1093

1094

1095

1096

1097

1098

1099

1100

1101

1102

1103

1104

1105

1106

1107

1108

1109

1110

1111

1112

1113

1114

1115

1116

1117

1118

1119

1120

1121

1122

1123

1124

1125

1126

1127

1128

1129

1130

1131

1132

1133

1134

1135

1136

1137

1138

1139

1140

1141

1142

1143

1144

1145

1146

1147

1148

1149

1150

1151

1152

1153

1154

1155

1156

1157

1158

1159

1160

1161

1162

1163

1164

1165

1166

1167

1168

1169

1170

1171

1172

1173

1174

1175

1176

1177

1178

1179

1180

1181

1182

1183

1184

1185

1186

1187

1188

1189

1190

1191

1192

1193

1194

1195

1196

1197

1198

1199

1200

1201

1202

1203

1204

1205

1206

1207

1208

1209

1210

1211

1212

1213

1214

1215

1216

1217

1218

1219

1220

1221

1222

1223

1224

1225

1226

1227

1228

1229

1230

1231

1232

1233

1234

1235

1236

1237

1238

1239

1240

1241

1242

1243

1244

1245

1246

1247

1248

1249

1250

1251

1252

1253

1254

1255

1256

1257

1258

1259

1260

1261

1262

1263

1264

1265

1266

1267

1268

1269

1270

1271

1272

1273

1274

1275

1276

1277

1278

1279

1280

1281

1282

1283

1284

1285

1286

1287

1288

1289

1290

1291

1292

1293

1294

1295

1296

1297

1298

1299

1300

1301

1302

1303

1304

1305

1306

1307

1308

1309

1310

1311

1312

1313

1314

1315

1316

1317

1318

1319

1320

1321

1322

1323

1324

1325

1326

1327

1328

1329

1330

1331

1332

1333

1334

1335

1336

1337

1338

1339

1340

1341

1342

1343

1344

1345

1346

1347

1348

1349

1350

1351

1352

1353

1354

1355

1356

1357

1358

1359

1360

1361

1362

1363

1364

1365

1366

1367

1368

1369

1370

1371

1372

1373

1374

1375

1376

1377

1378

1379

1380

1381

1382

1383

1384

1385

1386

1387

1388

1389

1390

1391

1392

1393

1394

1395

1396

1397

1398

1399

1400

1401

1402

1403

1404

1405

1406

1407

1408

1409

1410

1411

1412

1413

1414

1415

1416

1417

1418

1419

1420

1421

1422

1423

1424

1425

1426

1427

1428

1429

1430

1431

1432

1433

1434

1435

1436

1437

1438

1439

1440

1441

1442

1443

1444

1445

1446

1447

1448

1449

1450

1451

1452

1453

1454

1455

1456

1457

1458

1459

1460

1461

1462

1463

1464

1465

1466

1467

1468

1469

1470

1471

1472

1473

1474

1475

1476

1477

1478

1479

1480

1481

1482

1483

1484

1485

1486

1487

1488

1489

1490

1491

1492

1493

1494

1495

1496

1497

1498

1499

1500

1501

1502

1503

1504

1505

1506

1507

1508

1509

1510

1511

1512

1513

1514

1515

1516

1517

1518

1519

1520

1521

1522

1523

1524

1525

1526

1527

1528

1529

1530

1531

1532

1533

1534

1535

1536

1537

1538

1539

1540

1541

1542

1543

1544

1545

1546

#!/usr/local/bin/perl -w

################################################################################

# xmpBkp.pl - DB2 Full Backup Creation, Restore and List Tool

# OS System - AIX 4.3.x or Sun/Solaris 2.6 or higher

# Database - DB2 UDB V5.x or higher, works with DB2 V7.x

# Called by - Command Level or DBA Scripts

# Calls - xmpPerl.pm - xmp DBA Perl Package

# Getopt - Arguments processing

# Env - Access on environment variables

# English - Aliases on Perl variables

# Cwd - Current Working directory;

# DBI - Database Interface

# DBD::DB2 - DB2 UDB Interface

# Options - -d database - Database Name

# -a ... - Action: BACKUP [INCREMENTAL]/LIST/CHECK/RESTORE

# -f path - Path to Backup File

# -g ... - backup file generation(s) to keep or to restore

#-------------------------------------------------------------------------------

# Autor - GR /18.09.01 - V1.0 first version

# Changes - GR /12.10.01 - V1.1 now includes CHECK housekeeping function

# GR /17.10.01 - V1.2 now includes RESTORE function

# GR /06.11.01 - V1.3 File Maintenance in advance instead of afterwards,

# user/psw not required

# GR /09.11.01 - V1.3.1 Start/Stop Timestamp inserted, problems with

# stat module (printting size, uid deactivated)

# GR /09.11.01 - V1.3.2 Line Feed inserted

# GR /26.02.02 - V1.4 Corrected comments for calling the script,

# show and release unregistered (obsolete) bkps.

# GR /27.03.02 - V2.0 Incremental Backups and Restore

# GR /15.04.02 - V2.1.1 Different handling of -g argument

# GR /05.07.02 - V2.2 Process only Backup/Restore/Rollfwd Events

# GR /09.07.02 - V2.2.1 remaining calls of stat module deactivated

# GR /28.08.02 - V2.3 Set signal/return code if backup check failed

# GR /20.01.03 - V2.4 Show threads if DB in use (i.e. SQL1035N),

# added support for .gz files

# GR /03.03.04 - V2.5 Parm -x NOBUFF to suppress buffering

# GR /05.05.04 - V2.6 GetThreads copied from xmpTerm.pl

# GR /05.01.05 - V3.0 Handles multiple backup files

# GR /10.01.05 - V3.0 New -compress option

# GR /17.01.05 - V3.0.1 Sequence of DB2 backup command options corrected

# GR /28.04.05 - V3.1 Deflt=no buffering, BUFF+COMPRESS are incompatible!,

# applying both results in "SQL2009C There is not enough memory available",

# -x may contain any additional backup parm.

# Fixed problem with registered but not existing file when backup canceled.

# GR /25.10.06 - V4.0 Made COMPRESS'ed backups to the default,

# established DB2 environment, use DB2 table

# functions instead of LIST command output, new

# parm SYMBLINK for alternate Symbolic Link File Name,

# parm HARDLINK for alternate Hard Link File Name.

# GR /17.11.06 - V4.1 Provide more information of backup groups.

# New strategy for links: attach link id to original name.

# GR /20.11.06 - V4.1.1 Correction of SQL statement, using coalesce

# GR /13.04.07 - V4.2 New action SCRIPT to create RESTORE script file

# GR /29.05.07 - V4.2.1 More information about created RESTORE script file

# GR /25.07.08 - V4.2.2 BACKUP FULL and BACKUP DELTA=incremental added

# GR /29.07.08 - V4.3 Adaptions for DB2 V9.7

# GR /05.08.11 - V4.3.1 Small amendments

# GR /05.08.11 - V4.3.2 Verify opt_g to be positive on BACKUP

#-------------------------------------------------------------------------------

# Comments - PERL5LIB environment variable must point to perl module path!

# - Works only with OFFLINE backups!

# - Script shares sub-routines with xmpRHFTool.pl!

################################################################################

# Modules

################################################################################

use strict;

use Getopt::Long; # V3.0

use vars qw($opt_d $opt_u $opt_p $opt_a $opt_f $opt_g $opt_x);

use vars qw($opt_span); # V3.0

use vars qw($opt_compress); # V3.0

use vars qw($opt_symblink $opt_hardlink); # V4.0

use xmpPerl;

use Env;

use English;

use File::stat;

use File::Basename;

use Time::localtime;

use Cwd; # V4.0

use DBI;

use DBD::DB2::Constants;

use DBD::DB2;

################################################################################

# Declare/Init

################################################################################

my $VER = "4.3.2";

my $work = "";

my @work;

my $n_bkp_unregistered = 0; # no. of unregistered backups

my $dm = 0; # Feld für Dummy Werte

my $bkpfile; # Feld für Log Dateien

my $f_on = 0; # Flag on/off

my $n = 0; # Counter

my $i = 0; # Counter

my $ino; # File Info Block

my $DB2Version = xmpPerl::getDB2Version; # actual version of DB2

my $dbcfg_logpath = "";

my $dbcfg_nextlog = "";

my $dbcfg_firstlog = "";

my $dbcfg_logretain = "";

my $dbcfg_userexit = "";

my $dbcfg_logfilsiz = "";

my $dbcfg_logprimary = "";

my $dbcfg_consistent = "";

my $add_options = ""; # V3.1 additional options

my $span_files = ""; # V3.0 spanned bkp files

my ($dbh, $sth); # DB2 Access Handles

my $stmt = ""; # DB2 Statement

################################################################################

# Declare/Init: Event Table

################################################################################

my $e = 0; # Database Event Table

my %e_oper = (); my $c1; # Operation

my %e_obj = (); my $c2; # Object Type

my %e_ts = (); my $c3; # Timestamp

my %e_type = (); my $c4; # Backup Type

my %e_dev = (); my $c5; # Backup Device

my %e_1log = (); my $c6;

my %e_clog = (); my $c7;

my %e_path = (); my $cb; # Backup Path

my %e_bkp = (); my $cd; # Backup File exists?

my %e_start = (); my $c9;

my %e_end = (); my $ca;

my %e_comm = (); my $cc; # Comments/Commands

my %e_bkpfile =(); # Backup File

################################################################################

# Declare/Init: Thread Table

################################################################################

my $t = 0; # no. of Threads

my %t_authid = (); my $f1=""; # Auth ID

my %t_applname = (); my $f2=""; # Application Name

my %t_id = (); my $f3=""; # Thread ID

my %t_applid = (); my $f4; # Application ID

my %t_seq = (); my $f5; # Sequence

my %t_agentno = (); my $f6; # Number of Agents

my %t_node = (); my $f7; # Coordinator Node

my %t_pid = (); my $f8; # Coordinator PID

my %t_status = (); my $f9; # Status

my %t_statchgtm = (); my $fa; # Status Change Time

my %t_dbname = (); my $fb; # Database Name

my %t_path = (); my $fc; # DB Path

################################################################################

# Declare/Init: Backup File Table

################################################################################

my $bf = 0; # no. of backup files

my %bf_name = (); # backup file name

my %bf_basename = (); # backup file basename

my %bf_rhf = (); # registered in RHF

################################################################################

# Declare/Init: Backup Group Table (for spanned backups) # V3.0

################################################################################

my $bg = 0; # no. of backup files

my %bg_name = (); # backup group name

my %bg_type = (); # backup group type

my %bg_start = (); # backup group start

my %bg_path = (); # backup group path

my %bg_bkp = (); # backup group files exist

my %bg_fc = (); # backup group exist. files count

my %bg_ec = (); # backup group event count

################################################################################

# Output Report Format

################################################################################

format KOPF =

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

+--Operation: B=BACKUP L=LOAD R=RESTORE S=RUNST T=ALTER D=DROP F=ROLLF G=REORG

! +--Object: D=DB P=TABLESPACE T=TABLE

! ! +--Type: F=OFFLINE I=INCREMENT N=ONLINE C/R=ALTER-TS etc

! ! ! +--Device: A=ADSM D=DISK L=LOCAL P=PIPE T=TAPE S=SERVER

! ! ! ! ?: X=BACKUP FILE EXISTS

! ! ! *

* * * D 1st Log Curr Log Path

P O Timestmp+Seq T ? Start Time End Time Statement/Comment

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

format ZEILE =

@ @ @<<<<<<<<<<<<<<<< @ @ @<<<<<<<<<<< @<<<<<<<<<<< @<<<<<<<<<<<<<<<<<<<<<<<

$c1,$c2,$c3, $c4,$c5,$c6, $c7, $cb,

@ @<<<<<<<<<<<<< @<<<<<<<<<<<<< @<<<<<<<<<<<<<<<<<<<<<<<

$cd,$c9, $ca, $cc

################################################################################

# Report Format Options

################################################################################

$FORMAT_NAME = "ZEILE"; # $~

$FORMAT_TOP_NAME = "KOPF"; # $^

$FORMAT_FORMFEED = ""; # $^L

$FORMAT_LINES_PER_PAGE = 60; # $=, plus Lines für Kopf

################################################################################

# Check Argumente/Options

################################################################################

#getopts("d:a:f:g:x:"); # V3.0

GetOptions("d=s","u:s","p:s","a=s","f:s","g:i","x:s","span:i","symblink:s","hardlink:s","compress:s","script:s");

if (defined $opt_a ) { # Action Check

$opt_a = uc $opt_a; } # Action uppercase

else {

print "$0 - option '-a action' missing.\n";

SaySyntax();

}

if (! defined $opt_d ) # Datenbank Check

{print "$0 - Database not specified\n";

SaySyntax();

}

$opt_d = uc $opt_d; # Database uppercase

if (! defined $opt_u ) # user check V4.0

{$opt_u = xmpPerl::getDBAUser();

if ($opt_u eq "")

{print "$0 - user not specified and lP_LUS env variable not defined\n";

SaySyntax(); }

}

if (! defined $opt_p ) # User Password Check V4.0

{$opt_p = xmpPerl::getDBAPsw();

if ($opt_p eq "")

{print "$0 - missing user's password fehlt and lP_LPW env variable not defined\n";

SaySyntax(); }

}

if ( (defined $opt_a ) and # Action Check

($opt_a ne "BACKUP") and

($opt_a ne "BACKUP FULL") and # V4.2.2

($opt_a ne "BACKUP INCREMENTAL") and # V2.0

($opt_a ne "BACKUP DELTA") and # V4.2.2

($opt_a ne "RESTORE") and

($opt_a ne "SCRIPT") and # V4.2

($opt_a ne "LIST") and

($opt_a ne "CHECK") ) {

print "$0 - option '-a $opt_a' is not one of the valid actions.\n";

SaySyntax();

}

if ( (defined $opt_a ) and # Action

( ($opt_a eq "BACKUP")

or ($opt_a eq "BACKUP FULL") # V4.2.2

or ($opt_a eq "BACKUP DELTA") # V4.2.2

or ($opt_a eq "BACKUP INCREMENTAL") )and # V2.0

(! defined $opt_f) ) {

print "$0 - option '-f path' needed for action BACKUP [FULL|DELTA|INCREMENTAL].\n";

SaySyntax();

}

if ( (defined $opt_a ) and # Action SCRIPT V4.2

($opt_a eq "SCRIPT") and

(! defined $opt_f) ) {

print "$0 - option '-f path' needed to create restore script file.\n";

SaySyntax();

}

if ((defined $opt_g ) and # Generation Check

($opt_g !~ /[1234567890]/) ) { # not numererical

print "$0 - option '-g $opt_g ' invalid, specify numerical value 0-9.\n";

SaySyntax();

}

if ((defined $opt_g ) and # Generation Check

($opt_g lt "1") and # V4.3.2

( ($opt_a eq "BACKUP")

or ($opt_a eq "BACKUP FULL") # V4.2.2

or ($opt_a eq "BACKUP DELTA") # V4.2.2

or ($opt_a eq "BACKUP INCREMENTAL") ) ) {

print "$0 - option '-g $opt_g ' invalid for action BACKUP [FULL|DELTA|INCREMENTAL]|SCRIPT.\n";

print "(-g must be >0.) \n";

SaySyntax();

}

if ((defined $opt_span ) and # Backup File spanning V3.0

($opt_span !~ /[1234567890]/) ) { # not numerical

print "$0 - option '-span $opt_span' invalid, specify numerical value 0-9.\n";

SaySyntax();

}

if ((defined $opt_span ) and # Backup File spanning V3.0

($opt_span < 1) ) { # must be >= 1

print "$0 - option '-span $opt_span' invalid, must be >= 1.\n";

SaySyntax();

}

if ((defined $opt_symblink ) and # Symbolic File Link V 4.0

($opt_symblink eq "") ) { # ignore blank

undef $opt_symblink;

}

#if ((defined $opt_symblink) and # V4.1

# (defined $opt_span) ) {

# die "==> Options -span and -symblink are mutually exclusive - please correct.\n".

# "==> Script abends";

if ( (defined $opt_symblink)

and (-e $opt_symblink)

and (!-l $opt_symblink) ) {

die "==> Regular file already exists, cannot create $opt_symblink as Symbolic Link.\n".

"==> Script abends";

}

if ((defined $opt_hardlink ) and # Hard File Link V 4.0

($opt_hardlink eq "") ) { # ignore blank

undef $opt_hardlink;

}

#if ((defined $opt_hardlink) and # V4.1

# (defined $opt_span) ) {

# die "==> Options -span and -hardlink are mutually exclusive - please correct.\n".

# "==> Script abends";

################################################################################

# Equalize backup actions V4.2.2

################################################################################

if ($opt_a eq "BACKUP FULL") { $opt_a = "BACKUP" }

if ($opt_a eq "BACKUP DELTA") { $opt_a = "BACKUP INCREMENTAL" }

################################################################################

# Check existence of DB2, write information to console

################################################################################

xmpPerl::chkDB2();

xmpPerl::prtHeaderDB2("$0 - DB2 Backup/Restore - Version $VER");

print "Database and Action requested: $opt_d / $opt_a\n";

if ($opt_a eq "SCRIPT") { # V4.2.1

print "Name of RESTORE script file : $opt_f\n";

}

################################################################################

# DB Configuration Values

################################################################################

$work = xmpPerl::doCmdOut("db2 get db cfg for $opt_d | grep 'SQL'");

if ($work =~ "SQL1013N") {

die "==> This database could not be found. DB2 says ...\n".

"==> ".xmpPerl::trim($work)."\n".

"==> Script abends";

}

$work = xmpPerl::doCmdOut("db2 get db cfg for $opt_d | grep 'Path to log files'");

($work,$dbcfg_logpath) = split("=",$work);

$dbcfg_logpath = xmpPerl::trim($dbcfg_logpath);

$dbcfg_nextlog = xmpPerl::getDBCfgValue($opt_d,uc "Next active log file");

$dbcfg_firstlog = xmpPerl::getDBCfgValue($opt_d,uc "First active log file");

$dbcfg_logretain = xmpPerl::getDBCfgValue($opt_d,"LOGRETAIN");

$dbcfg_userexit = xmpPerl::getDBCfgValue($opt_d,"USEREXIT");

$dbcfg_logfilsiz = xmpPerl::getDBCfgValue($opt_d,"LOGFILSIZ");

$dbcfg_logprimary = xmpPerl::getDBCfgValue($opt_d,"LOGPRIMARY");

#$dbcfg_consistent = xmpPerl::getDBCfgValue($opt_d,uc "Database is consistent");

$dbcfg_consistent = xmpPerl::getDBConsistent($opt_d);

print "Log Path ....................: $dbcfg_logpath\n";

if ( (defined $dbcfg_nextlog )

&& ($dbcfg_nextlog ne "") ) {

print "Next active Log File ........: $dbcfg_nextlog\n";}

if ( (defined $dbcfg_firstlog )

&& ($dbcfg_firstlog ne "") ) {

print "First active Log File........: $dbcfg_firstlog\n";}

print "Log retain / User Exit ......: $dbcfg_logretain / $dbcfg_userexit\n";

print "Log Files: No. and Size in 4K: $dbcfg_logprimary * $dbcfg_logfilsiz\n";

print "Database currently consistent: $dbcfg_consistent\n";

if (defined $opt_x) { # V3.1

print "Additional Backup Parameters : $opt_x\n";

$add_options = $opt_x;

}

if (defined $opt_span) { # V3.0 Backup Spanning

print "Spanning Backups ............: $opt_span files\n";

}

#if ( ( (defined $opt_compress) # V3.0 Compression

# && ($opt_compress = "YES") )

# or ( !defined $opt_compress) ) { # V4.0 is default now

# $s_compress = "COMPRESS";

# print "Built-in Compression ........: YES (deflt.)\n";

print "Built-in Compression ........: YES (deflt.)\n"; # V4.0 as default

if (defined $opt_symblink) { # V4.0 Symbolic Link

print "Create Symbolic Link ........: YES\n";

}

if (defined $opt_hardlink) { # V4.0 Hard Link

print "Create Hard Link ............: YES\n";

}

################################################################################

# Obtain actual Backup Information, list if requested

################################################################################

print "--------------------------------------------------------------------------------\n";

GetEvents();

CheckFiles();

GetBackupGroups(); # V3.0

print "Current Backup Status .......: ".$bg." backup(s) registered in DB2 RHF\n";

print "- restorable Backups ........: ".CountExistingBackupGroups()." registered and file(s) ok\n";

print " - Full Backups ............: ".CountExistingFullBackupGroups()." registered and file(s) ok\n";

print "- existing Backup Files .....: ".CountExistingBackupFiles()." registered file(s)\n";

print "- missing Backup Files ......: ".CountMissingBackupFiles()." file(s) moved or deleted\n";

################################################################################

# Show existing backup files, registered in RHF or unregistered

################################################################################

#if (defined $opt_f) { # V4.2

if ( (defined $opt_f) and

($opt_a ne "SCRIPT") ) {

getBackupFiles();

print "- unregistered Backup Files : $n_bkp_unregistered\n";

print "- List of Backup Files ......: in path $opt_f\n";

for ($n = 1; $n <= $bf; $n++) {

if ($bf_rhf{$n}) {

print " - ".$bf_basename{$n}." registered in RHF\n";}

else { print " - ".$bf_basename{$n}." not registered\n";

}

else { print " (specify option -f to extend checks!)\n";

}

################################################################################

# Action SCRIPT: Create/check script file

################################################################################

if ( ($opt_a eq "SCRIPT") and

(defined $opt_f) ) {

if (! open(hFile, "> $opt_f") ) {

die "$0 cannot write into RESTORE script file $opt_f \n".

"abends";

}

close(hFile);

chmod 0775,$opt_f;

}

################################################################################

# Function LIST requested:

################################################################################

if ($opt_a eq "LIST") {

ListEvents();

}

################################################################################

# Function CHECK requested:

################################################################################

if ($opt_a eq "CHECK") {

ListEvents();

############################################################################

# For V7.x Backups check Backup File integrity

############################################################################

#if ( ($DB2Version =~ "V7") { # V3.0

if (xmpPerl::doCmdOut("which db2ckbkp") !~ "no db2ckbkp in"){ # V3.0

print "\nChecking Backup Files using db2ckbkp Utility:\n";

print "============================================\n";

$f_on = 0;

$i = 0; # V2.3

########################################################################

# Process internal Database Event Table

########################################################################

for ($n = 1; $n <= $e; $n++) {

if ( ($e_oper{$n} eq "B")

&& ($e_bkp{$n} eq "X")

&& ($e_bkpfile{$n} ne "") ) {

print "- Backup: $e_bkpfile{$n}\n";

Switch: {

if ($e_bkpfile{$n} =~ /\.Z/) {

print " File is compressed - cannot be checked.\n";

last Switch;}

if ($e_bkpfile{$n} =~ /.tar/) {

print " File is an archive - cannot be checked.\n";

last Switch;}

if ($e_bkpfile{$n} =~ /.gz/) { # V2.4

print " File is an archive - cannot be checked.\n";

last Switch;}

if ($e_bkpfile{$n} =~ /.zip/) {

print " File is compressed - cannot be checked.\n";

last Switch;}

print "Backup Check Utility started : ".xmpPerl::getTimestamp()."\n";

$dm = xmpPerl::doCmdOut("db2ckbkp -h $e_bkpfile{$n}");

print "Backup Check Utility ended ..: ".xmpPerl::getTimestamp()."\n";

if ($dm !~ / successful/) {

$f_on = 1;

print " File is not OK.\n";

print " $dm\n";

}

else { print " File is OK.\n";

print " $dm\n";

$i++;

}

######

www.ruban.de – DB2 experts web site

Search

DB2 forum

Topics

Archive

Ads

Archives from month » Februar, 2012

REXX procedure to call ADMIN_INFO_SYSPARM

Shell Script xmpSetInstance.sh – set DB2 environment

REXX procedure to execute DSNACCOX DB2-supplied stored procedure (V9)

DB2 Space calculation – from pages to tracks

CICS and MQ Explorer

Perl Script xmpBkp.pl – DB2 Backup, Restore, List Backup and Backup Script Creation