0

I have a IBM Content Manager with a DB2 database.

In ICMADMIN, I have a bunch of tables, and some of them contain a specific column (let's call it ID_CLIENT), which is an ID linking to one table (CLIENT).

How can I get the number of rows for each CLIENT from every table containing the ID_CLIENT column?

I know how to retrieve names of every table containing ID_CLIENT, but not how to join CLIENT on them dynamically.

select tabname from syscat.columns where colname='ID_CLIENT'

(let's call this query A)

So my pseudo query would look like:

select count(*) from CLIENT join (A) on CLIENT.ID_CLIENT = (A).ID_CLIENT
Kilazur
  • 3,089
  • 1
  • 22
  • 48
  • Hmm, one of the index-related ones _might_ have the info you need. Otherwise, you're going to need to write dynamic SQL - one `LEFT JOIN` (subquery aggregate) or separate query for each FK table. For a number of reasons (Especially security), you can't supply table names as parameters; you're going to have to construct the statement. – Clockwork-Muse Dec 12 '14 at 14:08
  • I don't think I can know beforehand the number of tables I'm going to have. I was hoping to be able to do this query by using some magic voodoo from icmstnlskeywords and such. – Kilazur Dec 12 '14 at 14:12
  • ...what is it you need this information for, then? – Clockwork-Muse Dec 12 '14 at 14:22
  • Well, all those tables contains documents. I'm trying to get the number of document for each client. – Kilazur Dec 12 '14 at 14:25
  • Shouldn't you usually _know_ what types of documents you're interested in? How many tables do you have in that case, anyways? I can't really think of a good reason for about 2 or 3 (that would reference the client id). I'd imagine you'd get more false positives from tables that have the fk-reference, but _aren't_ document-related. – Clockwork-Muse Dec 12 '14 at 14:30
  • The database isn't mine, so I can't help the choices made. – Kilazur Dec 12 '14 at 14:51

1 Answers1

1

It's possible to accomplish your goal by approaching it as a two-step process:

  1. Query the SYSCAT views to generate a separate SQL statement for each potential child table of CLIENT
  2. Capture and execute the SQL you generated

WITH ctbls ( tbl ) AS ( SELECT RTRIM( c.tabschema ) || '.' || c.tabname FROM syscat.columns c INNER JOIN syscat.tables t ON t.tabschema = c.tabschema AND t.tabname = c.tabname WHERE c.colname = 'CLIENT_ID' AND c.tabname <> 'CLIENT' -- we don't want to join CLIENT to itself AND t.type = 'T' -- if you want to work with tables only AND c.typename = 'INTEGER' -- if you want only want CLIENT_ID columns of a certain type ) -- Construct a left join between CLIENT and each table returned by the CTE above SELECT 'SELECT ''' || tbl || ''' AS childtablename, par.client_id, COUNT(*) AS childrows ' || 'FROM client par LEFT OUTER JOIN ' || tbl || ' chd ' || 'ON chd.client_id = par.client_id GROUP BY par.client_id;' FROM ctbls ;

Fred Sobotka
  • 5,252
  • 22
  • 32
  • Ah, so you CAN join dynamically on multiples tables thank to syscat.tables! Looks great, gonna try it and tell you how it went. – Kilazur Dec 19 '14 at 10:37
  • So basically, right now the only result I have is a one column (named 1) table containing the second part of the query for each table. Am I supposed to understand that I have to re-execute it myself, using a second query call? – Kilazur Dec 19 '14 at 11:00
  • Yes, as I stated at the beginning of my answer, it is a two-step process, and the SQL-generating query I provided is only the first step. – Fred Sobotka Dec 20 '14 at 00:21