sql question

MarkhamDBA · #1 (**permalink**) 08-24-09, 09:34

I need to create a list of non-SYS tables with the number of rows in each table. How would I do it?

Basic statement to create a list of tables is easy:
db2 -x “select rtrim(tabschema)||’.’||rtrim(tabname) from syscat.tables where type=’T’ and substr(tabschema,1,3) not in (‘SYS’) order by 1”

but I need help with the rest. Thanks in advance

n_i · #2 (**permalink**) 08-24-09, 09:49

All you need is to add the CARD column to the select list. This assumes that the statistics are current.

Cougar8000 · #3 (**permalink**) 08-24-09, 09:58

You might want to add Stats_time to see how old the stats are, which might help you identify how current your CARD count is.

What is the real purpose for this? Do you need an exact count?

MarkhamDBA · #4 (**permalink**) 08-24-09, 10:04

Quote:

Originally Posted by Cougar8000

You might want to add Stats_time to see how old the stats are, which might help you identify how current your CARD count is.

What is the real purpose for this? Do you need an exact count?

yes, i need exact row count. I am doing ADMIN_COPY_SCHEMA and need to verify that row count in tables did not change after copy. So I guess count(*) is the only option. Need some kind of subselect but can't figure out how to do it. Thank you

Cougar8000 · #5 (**permalink**) 08-24-09, 11:26

If you need an EXACT count then doing count(*) is how you get it.

Can you use shell scripting? if yes, I have a simple, but brutal script that will do it for all of your tables.

BTW. youa re aware of this, right? A backup must be taken after calling the ADMIN_COPY_SCHEMA, otherwise the copied tables will be inaccessible following recovery.

Lenny77 · #6 (**permalink**) 08-24-09, 11:28

You have to use Dynamic SQL for this.
I don't see any other solution.

Lenny

Cougar8000 · #7 (**permalink**) 08-24-09, 11:32

Another thing that I saw before, but did not raised a question until now. You are going to be excluding all SYS schema tables. What about your user tables? Are you going to be moving those as well.

Are you going to be moving data for all user and prod schemas?

MarkhamDBA · #8 (**permalink**) 08-24-09, 12:46

Quote:

Originally Posted by Cougar8000

Another thing that I saw before, but did not raised a question until now. You are going to be excluding all SYS schema tables. What about your user tables? Are you going to be moving those as well.

Are you going to be moving data for all user and prod schemas?

it's just that in this DB we have all tables under former DBA's name xxx. So I need to move all xxx. tables to DBA. tables (not touching SYS tables). Then I want to verify that not only all tables but ALL data were moved as well. This is our client's production DB so it is very important.

MarkhamDBA · #9 (**permalink**) 08-24-09, 12:49

Quote:

Originally Posted by Cougar8000

If you need an EXACT count then doing count(*) is how you get it.

Can you use shell scripting? if yes, I have a simple, but brutal script that will do it for all of your tables.

BTW. youa re aware of this, right? A backup must be taken after calling the ADMIN_COPY_SCHEMA, otherwise the copied tables will be inaccessible following recovery.

I can use scripting, but I thought it would be better to use SQL. So yes, please provide 'simple and brutal' script. Thank in advance

P.S. the tables actually can be accessed even without a backup (I checked). I will have to do backup anyway because of the change.

db2udbgirl · #10 (**permalink**) 08-24-09, 12:53

Following may help you

db2 -x "select 'select ''' || ltrim(rtrim(tabschema))||','||ltrim(rtrim(tabname) ) || ', '' , count(*) from ' || ltrim(rtrim(tabschema))||'.'||ltrim(rtrim(tabname) ) || ' with ur ;' from syscat.tables where tbspace='USERSPACE1' and ( tabschema like 'CIG%' ) order by tabschema, tabname " > sib_count.sql

Execute the sib_count.sql as regular sql file to generate count for all tables. You may need to modify the where clause per your requirement.

Cougar8000 · #11 (**permalink**) 08-24-09, 13:19

Quote:

Originally Posted by MarkhamDBA

it's just that in this DB we have all tables under former DBA's name xxx. So I need to move all xxx. tables to DBA. tables (not touching SYS tables). Then I want to verify that not only all tables but ALL data were moved as well. This is our client's production DB so it is very important.

How cool is that. People still haven't learned how to secure their environments

Here is the shell script. It will generate all the needed SQL and run it and pipe out results to a sorted file.

Code:

    #!/bin/ksh
   
  # This will generate a list of all tables
  db2 -x "select trim(tabschema) || '.' || trim(tabname)  from syscat.tables where tabschema  not like '%SYS%'" and tabschema <>  'your_old_dba_schema'>> list
   
  #This will do a small clean up
  sed -e 's/  //g' list>clean_list
   
  for i in `cat clean_list`
   
  do
   
  schema=$(echo $i | awk -F"." '{print $1}')
  name=$(echo $i | awk -F"." '{print $2}')
   
  #generate count 
  db2 "select '$schema.$name', count(*) from $schema.$name order by 1" >> count
   
   
  done

MarkhamDBA · #12 (**permalink**) 08-24-09, 14:15

Quote:

Originally Posted by db2udbgirl

Following may help you

db2 -x "select 'select ''' || ltrim(rtrim(tabschema))||','||ltrim(rtrim(tabname) ) || ', '' , count(*) from ' || ltrim(rtrim(tabschema))||'.'||ltrim(rtrim(tabname) ) || ' with ur ;' from syscat.tables where tbspace='USERSPACE1' and ( tabschema like 'CIG%' ) order by tabschema, tabname " > sib_count.sql

Execute the sib_count.sql as regular sql file to generate count for all tables. You may need to modify the where clause per your requirement.

it works! thanks so much

MarkhamDBA · #13 (**permalink**) 08-24-09, 14:21

Quote:

Originally Posted by Cougar8000

How cool is that. People still haven't learned how to secure their environments

Here is the shell script. It will generate all the needed SQL and run it and pipe out results to a sorted file.

Code:

    #!/bin/ksh
   
  # This will generate a list of all tables
  db2 -x "select trim(tabschema) || '.' || trim(tabname)  from syscat.tables where tabschema  not like '%SYS%'" and tabschema <>  'your_old_dba_schema'>> list
   
  #This will do a small clean up
  sed -e 's/  //g' list>clean_list
   
  for i in `cat clean_list`
   
  do
   
  schema=$(echo $i | awk -F"." '{print $1}')
  name=$(echo $i | awk -F"." '{print $2}')
   
  #generate count 
  db2 "select '$schema.$name', count(*) from $schema.$name order by 1" >> count
   
   
  done

thanks for the script. What do you mean by 'People still haven't learned how to secure their environments'?

Cougar8000 · #14 (**permalink**) 08-24-09, 14:26

Quote:

Originally Posted by MarkhamDBA

thanks for the script. What do you mean by 'People still haven't learned how to secure their environments'?

Statement is generally directed at management. Sounds like you have had a DBA that build production objects in his own schema. Now he is not there and you are scrambling. That is a major flow.

Lenny77 · #15 (**permalink**) 08-24-09, 14:43

Quote:

Originally Posted by db2udbgirl

Following may help you

db2 -x "select 'select ''' || ltrim(rtrim(tabschema))||','||ltrim(rtrim(tabname) ) || ', '' , count(*) from ' || ltrim(rtrim(tabschema))||'.'||ltrim(rtrim(tabname) ) || ' with ur ;' from syscat.tables where tbspace='USERSPACE1' and ( tabschema like 'CIG%' ) order by tabschema, tabname " > sib_count.sql

Execute the sib_count.sql as regular sql file to generate count for all tables. You may need to modify the where clause per your requirement.

It's interesting !

What count you count(*) ?

Why you use ltrim(rtrim(...)), but not STRIP(...) function ?

Thanks

Internet Services

Web Development

Digital Marketing

Consumer Tech