Determine the active segment table being used

[jsquizz]

Hi All,

wondering if anyone has a db2 query handy, or any suggestions on writing a query-  that will return the active app group segment table being loaded to.

Thanks!

[Greg Ira]

Maybe this will help for starters.  This is for DB2 on z/OS and shows the open segments for all AGs.  shows us % used.

SELECT DATE(CURRENT DATE) AS RUN_DATE FROM ONDDB.ARSUSER     
WHERE USERID LIKE 'ADMIN';                                     
SELECT SUBSTR(A.NAME,1,10) AS APP_GROUP,                       
DECIMAL((DECIMAL(S.INS_ROWS,11,2))/                           
(DECIMAL(S.MAX_ROWS/100.00,11,2)),11,2) PCT_USED,             
SUBSTR(S.TABLE_NAME,1,10) AS TABLE_P,                       
S.AGID, INTEGER(S.MAX_ROWS)                                   
FROM ONDDB.ARSSEG S, ONDDB.ARSAG A                           
WHERE SUBSTR(S.TABLE_NAME,1,2) <> 'SL'                         
AND S.AGID        = A.AGID                                     
AND S.CLOSED_DATE = 0                                         
ORDER BY A.NAME, S.START_DATE DESC;                           

[jsquizz]

Thanks Greg!

the solution I came up with, was something along the lines of-

joining arsseg/arsag, pulling table_name based on where app_group=xyz order by table_name then a tail -1 on the results.

I think that will work. Still testing and reviewing with my colleagues..seems a bit too easy :)

[rjrussel]

You can try this too:

arstblsp -a3 -h archive

This will show all the tables that are open for loading by application group.

[jsquizz]

You can try this too:

arstblsp -a3 -h archive

This will show all the tables that are open for loading by application group.

Makes sense that this would work,

however.

Code Select Expand

dev:bin# arstblsp -a 3 -I archive
ARS1107E An error occurred.  Contact your system administrator and/or consult the System Log.  File=, Line=

[Justin Derrick]

You have to tell it which Application Group you want the information for.  :)

-JD.

[Darrell Bryant]

Running arstblsp -a 3 without the AG name lists all AGs in the instance and the open table for each. For example:

arstblsp -a 3 -h QUSROND
ARS1304I Table still open for loading:  ApplGroup(Statements) Agid(243679) Table(CBCL1)           
ARS1304I Table still open for loading:  ApplGroup(REPORT1) Agid(243676) Table(BBCL1)             
ARS1304I Table still open for loading:  ApplGroup(ARSR1MST) Agid(225773) Table(DKKK1)             
ARS1304I Table still open for loading:  ApplGroup(TEGDB260) Agid(215817) Table(XTZJ1)   \
ARS1304I Table still open for loading:  ApplGroup(JOBLOG9LO) Agid(28417) Table(TVV1)
ARS1304I Table still open for loading:  ApplGroup(System Load) Agid(5011) Table(SA2)
ARS1304I Table still open for loading:  ApplGroup(System Log) Agid(5001) Table(SL2)
etc

If you specify the AG name then only that AG is listed. For example:

arstblsp -a 3 -g JOBLOG9LO -h QUSROND
ARS1304I Table still open for loading:  ApplGroup(JOBLOG9LO) Agid(28417) Table(TVV1)

[jsquizz]

Thanks JD/Darrell, this will work. Haven't had to use this command but this will be a GREAT help! I am still however, getting the same error.

Code Select Expand

preProd:root# arstblsp -a3 -h archive
ARS1107E An error occurred.  Contact your system administrator and/or consult the System Log.  File=, Line=

[greghidalgo]

Is 'archive'   the name of your OD instance?     And I think make sure you have space between -a and the '3' .   But I can generate the same ARS1107E when I put in an instance name that does not exist on my system.