Results 1 to 11 of 11
  1. #1
    Join Date
    May 2012
    Posts
    22

    Unanswered: Getting MAX values from multiple tables

    Hi all,

    I have to perform a data migration and merge three identical databases into one. I will be using one database as destination and the other two will be merged into that. All databases have around 600 tables.
    Now the problem is it cannot have duplicate IDs, primary keys, and columns with auto incremented values.
    I was thinking of getting MAX(ID) from all tables of DATABASE 1. Then to do something like
    UPDATE TABLE SET ID = ID + 10000 (Or maximum value) for DATABASE 2 AND DATABASE 3.
    I have to get the MAX of ALL (600) tables and then do the workaround. Can anyone tell me a way out to get the MAXIMUM of different columns in multiple tables?

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    You may write a script / SP
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    May 2012
    Posts
    22
    Yes, that's what I needed advise about. I need to find out a command to get MAX values from 600 tables!

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    in a script
    select 'select max(id) from '||rtrim(creator)||'.'||rtrim (name )||';' from sysibm.systables where creator='SCHEMA'
    this will generate a global select if the column is named id
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    May 2012
    Posts
    22
    The problem actually is that ALL tables have different column names for their columns with auto increment. So this command cannot work as its just for tables which have ID column name.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Not sure what you mean by "auto increment", but if those are identity columns, you can easily find them in SYSCAT.COLUMNS WHERE IDENTITY = 'Y'. It might be even faster to look up each identity next value from SYSCAT.COLIDENTATTRIBUTES.
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    May 2012
    Posts
    22
    Thank you. Let me explain you in detail.

    My database has 600 tables. Each table has an identity column with a different name.

    e.g. Person -> PersonID
    Student -> StudentID
    Grade - > GradeID

    ... and so on

    I have three databases and all have the same schema.
    I have to merge data of all three databases, so I want to take data of two databases and send it to one database. But it obviously would create duplicate values problems because of AUTO INCREMENT (IDENTITY) columns.

    So I will do the following steps:

    Step 1: Take maximum value of the identity column of all tables of DATABASE 1 (Which will be the destination database).
    Step 2: Do something like this on Database 2 and Database 3:
    For table Person, do PersonID=PersonID+10000(or max value from Person table of DATABASE 1)
    For table Student, do StudentID=StudentID+10000(or max value from Student table of DATABASE 1) .. and so on.. for 600 tables.

    ^^ The above step would be for both Database 2 and Database 3.

    Step 3: Use db2move to migrate data from Database 2 and Database 3 to Database 1.

    Problem with Step 1:

    I am still unable to find a command to get MAX values from all identity columns of all tables. If there is no command, I will have to do

    Select MAX(PersonID) from Person;
    Select MAX(StudentID) from Student;
    Select MAX(GradeID) from Grade;
    Select MAX(CourseID) from Course;
    .
    .
    .
    and so on for 600 tables. I will have to execute all these commands one by one. So I needed a script that could get MAX of identity columns of all tables of DB2INST1 schema.

    Please guide if anyone has done that before of have an idea about how it can be done. I am trying to save my time because this will be performed at live production servers as well.

  8. #8
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    This is not some question in a forum, this is a project. Lets me put it like this:
    "If you would hire me to do the job I'd go for a perl/DBI script because it is relatively easy to have 3 diff.database connections simultaneously open. I'd access the various SYSCAT tables to generate&fire the required SQL to the 3 databases (indeed: select max() from table). Based upon the results I'd generate the script".
    For more details pay me to write that script
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  9. #9
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    Ja, niks voor niks tegenwoordig... zo gaat dat...
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  10. #10
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    As I've said, you need a script or a SP. This should look like this:
    1. define a cursor in which you get table name and column name (SYSCAT.COLUMNS should help you)
    2. loop
    3. for each table, define a string that should look like: 'SELECT MAX(' || column_name || ') FROM ' || table_name
    4. run this SELECT into a temp variable or use a weakly typed cursor
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  11. #11
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by przytula_guy View Post
    Ja, niks voor niks tegenwoordig... zo gaat dat...
    inderdaad. een leuke klus is een leuke klus
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

Posting Permissions

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