Useful SQL Queries for Content Manager OnDemand
SQL Queries for CMOD Administrators
This is a series of example queries, which will show you how to extract useful information for CMOD Administrators from Content Manager OnDemand, that you can't get from the OnDemand Administrative Client.
All the examples below were run on AIX with DB2. Long output is truncated with the string "--snip--" for brevity.
Start the DB2 Command Line Client
$ db2 (c) Copyright IBM Corporation 1993,2007 Command Line Processor for DB2 Client 10.1.4 --snip-- db2 =>
Next we need to create a connection to the database.
Connect to the Database
db2 => connect to archive Database Connection Information Database server = DB2/AIX64 10.1.4 SQL authorization ID = ARCHIVE Local database alias = ARCHIVE
Which tables are available for us to query?
db2 => list tables for user Table/View Schema Type Creation time ------------------------------- --------------- ----- -------------------------- ACA1 ARCHIVE T 2015-05-21-12.05.47.942333 ADA1 ARCHIVE T 2015-05-21-11.54.41.841925 AFA1 ARCHIVE T 2015-05-10-09.15.57.588945 ARSAG ARCHIVE T 2015-05-08-19.15.25.181919 ARSAG2FOL ARCHIVE T 2015-05-08-19.15.25.349944 ARSAGFLD ARCHIVE T 2015-05-08-19.15.25.452429 ARSAGFLDALIAS ARCHIVE T 2015-05-08-19.15.25.528572 ARSAGINDEX ARCHIVE T 2015-05-08-19.15.25.599789 ARSAGPERMS ARCHIVE T 2015-05-08-19.15.25.671429 ARSANN ARCHIVE T 2015-05-08-19.15.25.788716 ARSAPP ARCHIVE T 2015-05-08-19.15.25.912664 ARSAPPUSR ARCHIVE T 2015-05-08-19.15.26.042617 ARSCAB ARCHIVE T 2015-05-08-19.15.26.164461 ARSCAB2FOL ARCHIVE T 2015-05-08-19.15.26.259013 ARSCABPERMS ARCHIVE T 2015-05-08-19.15.26.328841 ARSCFSODWORK ARCHIVE T 2015-05-08-19.15.26.397442 ARSFOL ARCHIVE T 2015-05-08-19.15.26.521362 ARSFOLFLD ARCHIVE T 2015-05-08-19.15.26.607325 ARSFOLFLDUSR ARCHIVE T 2015-05-08-19.15.26.676858 ARSFOLPERMS ARCHIVE T 2015-05-08-19.15.26.748387 ARSGROUP ARCHIVE T 2015-05-08-19.15.26.832365 ARSHOLD ARCHIVE T 2015-05-08-19.15.26.917837 ARSHOLDMAP ARCHIVE T 2015-05-08-19.15.27.005190 ARSHOLDPERMS ARCHIVE T 2015-05-08-19.15.27.075496 ARSHOLDWORK ARCHIVE T 2015-05-08-19.15.27.139762 ARSLOAD ARCHIVE T 2015-05-08-19.15.27.218273 ARSNAMEQ ARCHIVE T 2015-05-08-19.15.27.314828 ARSNODE ARCHIVE T 2015-05-08-19.15.27.449770 ARSPRT ARCHIVE T 2015-05-08-19.15.27.543703 ARSPRTOPTS ARCHIVE T 2015-05-08-19.15.27.640705 ARSPRTUSR ARCHIVE T 2015-05-08-19.15.27.778551 ARSRES ARCHIVE T 2015-05-08-19.15.27.848941 ARSSEG ARCHIVE T 2015-05-08-19.15.27.921419 ARSSET ARCHIVE T 2015-05-08-19.15.28.016458 ARSSYS ARCHIVE T 2015-05-08-19.15.28.100148 ARSUSER ARCHIVE T 2015-05-08-19.15.28.195988 ARSUSRGRP ARCHIVE T 2015-05-08-19.15.28.309638 ARSUSRGRPID ARCHIVE T 2015-05-08-19.15.28.392670 AUA1 ARCHIVE T 2015-05-10-19.47.51.712855 BDA1 ARCHIVE T 2015-05-09-15.25.45.061520 BEA1 ARCHIVE T 2015-05-10-20.03.56.423789 BFA1 ARCHIVE T 2015-05-10-16.41.30.016719 BHA1 ARCHIVE T 2015-05-10-19.46.08.765555 --snip--
The tables starting with ARS are internal configuration tables for CMOD. All of the data you see can be found in these tables. Although the contents of the tables are only briefly documented, there is plenty of interesting data to be found inside them to help you solve problems and troubleshoot Content Manager OnDemand.
And what are those other table names? They contain the index data with the fields you define in Application Groups with the OnDemand Administrator Client. Keep reading!
What are the fields and types in ARSAG?
db2 => describe table arsag
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
NAME SYSIBM VARCHAR 60 0 No
DESCRIPTION SYSIBM VARCHAR 120 0 No
AGID SYSIBM INTEGER 4 0 No
AGID_NAME SYSIBM VARCHAR 8 0 No
DB_SEG SYSIBM CHARACTER 1 0 No
ANN_TYPE SYSIBM CHARACTER 1 0 No
DB_EXP_DATE SYSIBM INTEGER 4 0 No
DB_MGRT_DATE SYSIBM INTEGER 4 0 No
SM_CACHE_DOC SYSIBM INTEGER 4 0 No
RESGRP SYSIBM INTEGER 4 0 No
LOAD_ID SYSIBM INTEGER 4 0 No
LOG SYSIBM INTEGER 4 0 No
LOAD_ID_SUFFIX SYSIBM VARCHAR 8 0 No
MGRT_CACHE_DATE SYSIBM INTEGER 4 0 No
REIMPORT_EXP_DATE SYSIBM INTEGER 4 0 No
SID SYSIBM INTEGER 4 0 No
EXPIRE_TYPE SYSIBM CHARACTER 1 0 No
SEG_ROWS SYSIBM INTEGER 4 0 No
SEG_ID SYSIBM INTEGER 4 0 No
OBJ_SIZE SYSIBM INTEGER 4 0 No
TYPE SYSIBM INTEGER 4 0 No
UPD_USERID SYSIBM VARCHAR 128 0 Yes
UPD_DATE SYSIBM BIGINT 8 0 Yes
LAST_DOC_DATE SYSIBM BIGINT 8 0 Yes
MIGR_SRVR_STR SYSIBM VARCHAR 254 0 Yes
SM_CACHE_DELTA SYSIBM INTEGER 4 0 Yes
DATABASE_NAME SYSIBM VARCHAR 8 0 Yes
SM_CACHE_RES SYSIBM INTEGER 4 0 Yes
28 record(s) selected.
These fields are all for internal use by CMOD to provide the functionality of the product. You can also see the data types (CHARACTER, VARCHAR, INTEGER) and the lengths of the fields. All these fields contain information you see in the Content Manager OnDemand Administrator Client -- but some are strictly to help CMOD work.
What are the AGID and AGID_NAME fields?
db2 => select agid,agid_name,name from arsag
AGID AGID_NAME NAME
----------- --------- ------------------------------------------------------------
5007 SA System Load
5001 SL System Log
2 record(s) selected.
The AGID is the 'number' of the Application Group. This number is used in other tables to establish a relationship -- for example, to Applications, or Storage Nodes, or Groups. The AGID_NAME is a three-character alphabetical label for the Application Group. This AGID_NAME field is used in the cache filesystem, database table names, and Storage Sets. In the case of Storage Sets, the AGID_NAME value is used as the 'Filespace Name' in Tivoli Storage Manager (now 'IBM Spectrum Archive').
How is the AGID used in Content Manager OnDemand?
If we describe the Application Table ARSAPP, we can see it also has an AGID field.
db2 => describe table arsapp
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
NAME SYSIBM VARCHAR 60 0 No
DESCRIPTION SYSIBM VARCHAR 120 0 No
AGID SYSIBM INTEGER 4 0 No
AID SYSIBM INTEGER 4 0 No
DOC_TYPE SYSIBM CHARACTER 1 0 No
--snip--
We can use this common field as a relation between tables, to build powerful queries.
db2 => select a.agid,a.agid_name,a.name,b.aid,b.name from arsag a, arsapp b where a.agid=b.agid
AGID AGID_NAME NAME AID NAME
----------- --------- ---------------------------------------- ----------- -------------------------------
5007 SA System Load 5008 System Load - No Log Document
5007 SA System Load 5009 System Load - Log Document
5001 SL System Log 5002 System Log - N/A Document
5001 SL System Log 5003 System Log - No Document
5001 SL System Log 5004 System Log - Text Document
5 record(s) selected.
The values on the left (from the ARSAG table) are repeated, because the values (AGIDs 5007 and 5001) appear multiple times in the ARSAPP table. Notice that Applications have their own unique identifiers as well, separate from AGIDs.