Useful SQL Queries for Content Manager OnDemand - Revision history

Jderrick: /* Can I export this information to a spreadsheet? */

2017-02-15T15:50:29Z

Can I export this information to a spreadsheet?

← Older revision		Revision as of 15:50, 15 February 2017
Line 229:		Line 229:
	"UNIFORM",38344		"UNIFORM",38344
	"VICTOR",56579		"VICTOR",56579


			= Related Links =
			[http://www.odusergroup.org/forums/index.php?topic=2186.0 Counting loads by Indexer from the IBM OnDemand User Group Forums]

Jderrick: Added link to IBM CMOD database tables.

2017-02-13T11:29:22Z

Added link to IBM CMOD database tables.

← Older revision		Revision as of 11:29, 13 February 2017
Line 78:		Line 78:
	--snip--		--snip--

	The tables starting with ARS are internal configuration tables for CMOD. All of the data you see inside the IBM CMOD client can be found in these tables. Although the contents of the tables are only briefly documented in the IBM CMOD documentation, there is plenty of interesting data to be found inside them to help you solve problems and troubleshoot issues. Check out the [Content Manager OnDemand Database Tables] for more information about the tables, fields, and their contents.		The tables starting with ARS are internal configuration tables for CMOD. All of the data you see inside the IBM CMOD client can be found in these tables. Although the contents of the tables are only briefly documented in the IBM CMOD documentation, there is plenty of interesting data to be found inside them to help you solve problems and troubleshoot issues. Check out the [[Content Manager OnDemand Database Tables]] for more information about the tables, fields, and their contents.

	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!		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!

Jderrick: /* Which tables are available for us to query? */

2017-02-13T11:28:49Z

Which tables are available for us to query?

← Older revision		Revision as of 11:28, 13 February 2017
Line 78:		Line 78:
	--snip--		--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.		The tables starting with ARS are internal configuration tables for CMOD. All of the data you see inside the IBM CMOD client can be found in these tables. Although the contents of the tables are only briefly documented in the IBM CMOD documentation, there is plenty of interesting data to be found inside them to help you solve problems and troubleshoot issues. Check out the [Content Manager OnDemand Database Tables] for more information about the tables, fields, and their contents.

	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!		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!

Jderrick: Added section on exporting Content Manager OnDemand Application Group row counts to Comma Delimited Format.

2016-07-05T14:37:53Z

Added section on exporting Content Manager OnDemand Application Group row counts to Comma Delimited Format.

← Older revision		Revision as of 14:37, 5 July 2016
Line 167:		Line 167:
	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.		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.

	== How many documents are in an Application ~~Group~~? ==		== How many documents are in CMOD Application Groups? ==

	You may want to know how many documents are currently in an Application Group. You can get this information by querying the ARSSEG table in the Content Manager OnDemand database.		You may want to know how many documents are currently in an Application Group. You can get this information by querying the ARSSEG table in the Content Manager OnDemand database.
Line 206:		Line 206:
	System Log 241680991		System Log 241680991

	== ~~How many documents are in the whole system~~? ==		=== Can I export this information to a spreadsheet? ===

			Yup! Prefix your select statement with an export command, specifying the "Delimited" format, which uses commas (",") by default:

			db2 "export to AG_Row_Count.csv of DEL select arsag.name,sum(arsseg.ins_rows-arsseg.del_rows) as Total_Rows from arsag,arsseg where arsag.agid=arsseg.agid group by arsag.name"

			SQL3104N The Export utility is beginning to export data to file "AG_Row_Count.csv".
			SQL3105N The Export utility has finished exporting "205" rows.

			Number of rows exported: 205

			Now let's check the results:

	~~=== Can I export this information to a spreadsheet? ===~~		$ ls -l AG_Row_Count.csv
			-rw-r--r-- 1 archive sysadm1 3847 Jul 05 09:52 AG_Row_Count.csv

			$ more AG_Row_Count.csv
			"QUEBEC",6198
			"ROMEO",12971
			"SIERRA",23032
			"TANGO",18902
			"UNIFORM",38344
			"VICTOR",56579

Jderrick: Initial Edit: How many documents are in a Content Manager OnDemand Application Group?

2016-07-05T14:29:18Z

Initial Edit: How many documents are in a Content Manager OnDemand Application Group?

← Older revision		Revision as of 14:29, 5 July 2016
Line 168:		Line 168:

	== How many documents are in an Application Group? ==		== How many documents are in an Application Group? ==

			You may want to know how many documents are currently in an Application Group. You can get this information by querying the ARSSEG table in the Content Manager OnDemand database.

			This SQL Query provides the number of inserted and deleted rows from the Application Group Tables in OnDemand.

			db2 "select arsag.name,arsseg.table_name,arsseg.ins_rows,arsseg.del_rows from arsag,arsseg where arsag.agid=arsseg.agid order by arsseg.table_name"

			NAME TABLE_NAME INS_ROWS DEL_ROWS
			------------------------------------------------------------ ------------------ ----------- -----------
			ALPHA RQA1 4454 0
			BRAVO RSA1 19670 1
			CHARLIE RUA1 18878 1
			DELTA RVA1 153 5
			ECHO RWA1 118 14
			FOXTROT RYA1 7989 0
			GOLF SBA1 195077 26
			HOTEL SDB1 444632 0
			INDIA SIB1 8228 0
			System Log SL10 2500001 0
			System Log SL11 2500000 0
			System Log SL12 2500002 0
			System Log SL13 2500004 0

			But maybe you don't want to do the math, and instead want DB2 to count the records for you, and summarize by Application Group, instead of listing each table:

			db2 "select arsag.name,sum(arsseg.ins_rows-arsseg.del_rows) as Total_Rows from arsag,arsseg where arsag.agid=arsseg.agid group by arsag.name"

			NAME TOTAL_ROWS
			------------------------------------------------------------ -----------
			JULIET 1015276
			KILO 32133
			LIMA 27200
			MONTREAL 10726
			NANCY 148
			OSCAR 42072
			PAPA 886
			System Log 241680991

	== How many documents are in the whole system? ==		== How many documents are in the whole system? ==

	=== Can I export this information to a spreadsheet? ===		=== Can I export this information to a spreadsheet? ===

Jderrick: Added section on counting documents in CMOD.

2016-07-05T13:58:14Z

Added section on counting documents in CMOD.

← Older revision		Revision as of 13:58, 5 July 2016
Line 166:		Line 166:

	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.		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.

			== How many documents are in an Application Group? ==

			== How many documents are in the whole system? ==

			=== Can I export this information to a spreadsheet? ===

Jderrick: Changed title of main heading.

2016-04-14T01:09:00Z

Changed title of main heading.

← Older revision		Revision as of 01:09, 14 April 2016
Line 1:		Line 1:
	{{TOCright}}		{{TOCright}}
	= ~~Interesting~~ SQL Queries for CMOD =		= 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.		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.

Jderrick: Initial Edit.

2016-04-14T01:08:09Z

Initial Edit.

New page

{{TOCright}}
= Interesting SQL Queries for CMOD =

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.