Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2012
    Posts
    3

    Unanswered: Alter table vs. Create table

    Our application is tied to information in DB2 tables. On occasion, to support new software features, it becomes necessary to add or alter a column. At this time we have a db installer tool that creates the tables from scratch. The problem with this is all existing table information is lost. During the db installation we already know that the table exists (we warn the user it will be deleted and recreated). What I would like to do is query the existing table and determine the difference between the existing table structure (column names, data type etc) and what is about to be created then use ALTER table commands to modify the existing table thus preserving the existing data. New columns are ALWAYS added at the end. I'm not sure of the best way this can be done. Any thoughts on this would be helpful and appreciated.

    Here is an example of the create command used in our code:
    CREATE TABLE LOTINFO_TBL (LOTID CHAR(32) NOT NULL,SYNC CHAR(3),TS TIMESTAMP NOT NULL,PARTNO CHAR(32) NOT NULL,FC CHAR(3),PC CHAR(3),LC CHAR(3))

    As an example, maybe we're supporting a new feature that requires an additional column - BC CHAR(10). This column is to be added to the end of the LOTINFO_TBL. If the table already exists how can I quickly know its structure and that is missing that column and do an ALTER?

  2. #2
    Join Date
    Apr 2012
    Posts
    1,143
    Provided Answers: 27
    Looks like the design of the db-installer tool is not fit for production use, where the normal pattern is to preserve existing data when table alterations are required.
    Depending on your version of DB2 and the operating-system on which the DB2-server runs, consider using the stored procedure ALTOBJ which handles many similar requirements - you need to do the research to avoid re-inventing the wheel.
    See the documentation at URL below
    https://www.ibm.com/support/knowledg.../r0011934.html

  3. #3
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    294
    Provided Answers: 45
    Use the SYSCAT.COLUMNS view to get columns metadata of your tables.
    Regards,
    Mark.

Tags for this Thread

Posting Permissions

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