Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2010
    Posts
    7

    Unanswered: Verification that data in tables exists

    Hi,
    I have a task to verify that data in tables exists against huge amount of tables (>1000).
    This operation should be processed as fast as possible. (Main criteria)
    I'm using .Net provider and z/OS v8.

    For now I see following decision:
    To the server I will send 1 statement that contains several following queries:
    SELECT 1 FROM a FETCH FIRST ROW ONLY FOR READ ONLY WITH UR;
    SELECT 2 FROM b FETCH FIRST ROW ONLY FOR READ ONLY WITH UR;
    ....
    (To prevent script is too complex error I will split it up by 500)

    If you see more productive approaches then you are welcome in this topic.
    Thanks in advance.

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    How about read the catalog views and only find tables that are empty?
    Dave

  3. #3
    Join Date
    Nov 2010
    Posts
    7
    Catalog views don't have such information.
    (Correct me if I'm wrong)
    There are info about statistics only but it can be not actual.

  4. #4
    Join Date
    Apr 2005
    Location
    USA
    Posts
    130
    I think you can get the table count from catalogs provided if your stats are up to date.

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    If you have statistics collected on the tables, you can query the catalog and find tables without any rows. But you are right that those statistics could be outdated. However, in such a situation you probably have a different problem anyway: if the stats are not up to date, you have a good chance that the optimizer is picking sub-optimal access paths. So it is usually a good idea to make sure the statistics are more or less up to date.

    What you could do is this: for all important tables you keep the stats up to date and rely on the stats. For all other tables, you have to query the table itself.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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