Stogroup question - DB2 v8

Philip Sevetson

Stogroup question - DB2 v8
Scott,

I hope your problem hasn't remained unsolved -- I did have a question on
looking at your description: Did your two STOGROUPs have different
high-level qualifiers? I recall DB2 having trouble finding datasets after I
changed the STOGROUP for the defined tablespaces. Could this be a case of
that?


On 12/29/06, Hodgin, Scott <[login to unmask email]> wrote:
>
> This past weekend, I alter a bunch of indexes to use a different stogroup
> with a bunch of packs assigned to it, but I did not reorg the indexes.
>
> I've recently had some jobs blowing up due to inability to extend datasets
> for various indexes.
>
> I'm puzzled. So I have to reorg/rebuild the indexes to get db2 to tabke
> advantage of the new stogroup. The original stogroup that the index was
> assigned to was a single pack which is now full. I would have thought the
> extend request would have just looked on another pack assigned to the new
> stogroup.
>
> Thanks,
>
> Scott Hodgin
>
> Database Administrator, (MCTS-SQL Server 2005)
>
> South Carolina Farm Bureau Insurance Company
>
> *[login to unmask email]* <[login to unmask email]>
>
> ---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
> page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
> select "Join or Leave the list". The IDUG DB2-L FAQ is at
> http://www.idugdb2-l.org. The IDUG List Admins can be reached at
> [login to unmask email] Find out the latest on IDUG conferences
> at http://conferences.idug.org/index.cfm




--
--Phil Sevetson
IBM Certified Solutions Expert -- DB2 V7.1 Database Administration for
OS/390
IBM Certified Database Administrator -- DB2 Universal Database V8.1 for zOS
The Reserve
New York, NY, USA
ANY OPINIONS OR RECOMMENDATIONS EXPRESSED IN THIS EMAIL
ARE MY OWN, NOT MY EMPLOYER'S OR IBM'S.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

David Seibert

Re: Stogroup question - DB2 v8
(in response to Philip Sevetson)
Hello Philip.

I have a piece of Rexx / DSNREXX / SQL which takes a stab at
generating the 4 high-level nodes of the VSAM dataset for a DB2 table if
you're using Stogroups.

I've never bothered to look in doc to see if this should work outside
our environment or if it relies on some naming convention we adhere to
here.

In any case, here is the Rexx. Of course it comes without warranty or
any other lawyerly features. It may or may not work for you. It may or
may not make your employer more enamored of your value. It most likely
will not have any effect on any bunions you might have.

Try it.
Dave
-------------------------------code --------------------------
/* Rexx */
/* If tb then get tsname,dbname & join systablepart and systables
and build hilev dsname from VCATNAME, literal, DBname, TSname */
Arg tbnameCreator ssid Tr_parm

If Tr_parm <> '' then /* Debug code */
do;trace_on=1;Tr_stmt='TRACE 'Tr_parm;interpret Tr_stmt;end
else trace_on = 0

if pos('.',tbnameCreator) > 0 then
do
parse var tbnameCreator tbcreator '.' tbname
creatorClause="AND T.CREATOR='"tbcreator"'"
end
else
do
tbname=tbnameCreator
creatorClause=''
end

if ssid = '' | ssid = '.' then
ssid = dfltdb2() /* set default subsys id */

'SUBCOM DSNREXX'
if rc then ,
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
Address DSNREXX

'CONNECT' ssid

SQLstmt = ,
"SELECT Left(strip(tp.VCATNAME)||'.DSNDBD.'",
"||strip(T.DBNAME)||'.'||strip(T.TSNAME)||' ',40)||",
"'C='||T.CREATOR",
"FROM SYSIBM.SYSTABLEPART TP, SYSIBM.SYSTABLES T",
"WHERE TP.TSNAME=T.TSNAME AND T.NAME='"tbname"'",
"AND TP.DBNAME=T.DBNAME " creatorClause,
"AND T.type='T'"

"EXECSQL DECLARE C1 CURSOR FOR S1"
"EXECSQL PREPARE S1 INTO :OUTSQLDA FROM :SQLSTMT"
if SQLcode <> 0 then
do;say 'Prepare failed; ';call D_SQLCA;exit;end


"EXECSQL OPEN C1"
if SQLcode <> 0 then
do;say 'Open failed; ';call D_SQLCA;exit;end

if SQLcode = 0 then,
do
"EXECSQL FETCH C1 USING DESCRIPTOR :OUTSQLDA"
if SQLcode <> 0 then
do;say 'Fetch failed; ';call D_SQLCA;exit;end
j=0
do Until SQLcode <> 0
Line = ''
do I = 1 To OUTSQLDA.SQLD
Line = Line OUTSQLDA.I.SQLDATA
end I
j=j+1
stgln.j = Line

"EXECSQL FETCH C1 USING DESCRIPTOR :OUTSQLDA"
if SQLcode = 100 THEN
do
"EXECSQL CLOSE C1"
leave /* End of rows; close cursor and depart loop */
end
else
if SQLcode <> 0 then
do;say 'Fetch failed; 'call display_SQLCA;exit;end
end
end


if trace_on then interpret Tr_stmt /* Debug code */

S_RC = RXSUBCOM('DELETE','DSNREXX','DSNREXX')

If 'SYSVAR'('SYSISPF') = 'ACTIVE' then
do
Parse Value Time() With hh ':' mm ':' ss

tnode = "T"hh||mm||ss

address TSO
"ALLOC FI("tnode") NEW DELETE SPACE(1 5) UNIT(sysda)",
"LRECL(80) RECFM(F B) BLKSIZE(3120)"

Push ' '
Push copies('-',80)
Push Center('Probable High level VSAM dataset node(s) for Table: ',
||tbname,80)
address TSO "EXECIO 3 DISKW" tnode
address tso "EXECIO" j "DISKW" tnode "(STEM stgln. FINIS"
address ispexec
"LMINIT DATAID(DID) DDNAME("tnode") ENQ(SHR)"
"BROWSE DATAID("did")"
"LMFREE DATAID("did")"
Address "TSO" "FREE FI("tnode")"
end
else
do
say Center('Probable High level VSAM dataset node(s) for Table: ',
||tbname,80)
say copies('-',80)
say ' '
do i = 1 to j
say stgln.j
end
end

Exit
D_Sqlca: /* Display SQLCA fields */

Say 'SQLCode:'sqlcode
If sqlwarn.0 <> ' ' then
do
say 'SQLWarns: 123456790'
say ' >'sqlwarn.1||sqlwarn.2||sqlwarn.3||sqlwarn.4||sqlwarn.5,
sqlwarn.6||sqlwarn.7||sqlwarn.8||sqlwarn.9||sqlwarn.10||'<'
end
do i=1 to 6
say 'SqlErrd'i sqlerrd.i
end

Say "SQLState="SQLSTATE
Say "SQLErrp ="SQLERRP
Say "SQLErrmc ="SQLERRMc '; SQLErrm: 'sqlerrm
return
------------------------------- end code ----------------------
The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Philip Sevetson

Re: Stogroup question - DB2 v8
(in response to David Seibert)
LOL. Thanks, Dave, but I haven't been working with high-level qualifiers
for a while. I'll keep this somewhere useful, though. I was just
interested in Scott's problem for its own sake.

--Phil


On 1/5/07, Seibert, Dave <[login to unmask email]> wrote:
>
> Hello Philip.
>
> I have a piece of Rexx / DSNREXX / SQL which takes a stab at
> generating the 4 high-level nodes of the VSAM dataset for a DB2 table if
> you're using Stogroups.
>
> I've never bothered to look in doc to see if this should work outside
> our environment or if it relies on some naming convention we adhere to
> here.
>
> In any case, here is the Rexx. Of course it comes without warranty or
> any other lawyerly features. It may or may not work for you. It may or
> may not make your employer more enamored of your value. It most likely
> will not have any effect on any bunions you might have.
>
> Try it.
> Dave
> -------------------------------code --------------------------
> [code snipped for reply]
>
--
--Phil Sevetson
IBM Certified Solutions Expert -- DB2 V7.1 Database Administration for
OS/390
IBM Certified Database Administrator -- DB2 Universal Database V8.1 for zOS
The Reserve
New York, NY, USA
ANY OPINIONS OR RECOMMENDATIONS EXPRESSED IN THIS EMAIL
ARE MY OWN, NOT MY EMPLOYER'S OR IBM'S.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm