Results 1 to 15 of 15

Thread: sql question

  1. #1
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399

    Unanswered: sql question

    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
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    All you need is to add the CARD column to the select list. This assumes that the statistics are current.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    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?
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  4. #4
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    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
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  5. #5
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    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.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    You have to use Dynamic SQL for this.
    I don't see any other solution.

    Lenny

  7. #7
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    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?
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  8. #8
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    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.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  9. #9
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    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.
    Last edited by MarkhamDBA; 08-24-09 at 13:58.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  10. #10
    Join Date
    Mar 2006
    Location
    Tuticorin, India
    Posts
    103
    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.

  11. #11
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    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
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  12. #12
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    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
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  13. #13
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    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'?
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  14. #14
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    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.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  15. #15
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •