Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
5 replies Latest Post - ‏2012-07-05T13:08:00Z by DRCruikshank
SystemAdmin
SystemAdmin
374 Posts
ACCEPTED ANSWER

Pinned topic Creating a Physical model for DB2: Editing Generate Type to DEFAULT?

‏2012-04-30T09:38:35Z |
Hi,

I have a logical model that I have created a DB2 specific physical model of. The problem is that the "Generated type" is set automatically to "ALWAYS" but I would need it to be "BY_DEFAULT" for all tables. I haven't found a place to change it anywhere for all tables. It is only possible to change it after the generation has been done.

Alternatively, is it possible to use some "ALTER" query that would take all tables and change them from GENERATED ALWAYS to GENERATED BY DEFAULT?

Thanks in advance!
Updated on 2012-07-05T13:08:00Z at 2012-07-05T13:08:00Z by DRCruikshank
  • DRCruikshank
    DRCruikshank
    6 Posts
    ACCEPTED ANSWER

    Re: Creating a Physical model for DB2: Editing Generate Type to DEFAULT?

    ‏2012-06-09T17:38:15Z  in response to SystemAdmin
    You can generate ALTER TABLE statements from the catalog views. Here is an example using DB2 for i:

    SELECT 'ALTER TABLE ' || RTRIM ( TABLE_SCHEMA )
    || '.' || RTRIM ( TABLE_NAME )
    || ' ALTER COLUMN '
    || RTRIM ( COLUMN_NAME )
    || ' SET GENERATED BY DEFAULT;' AS SQL_STRING
    FROM QSYS2.SYSCOLUMNS
    WHERE TABLE_SCHEMA = 'DCRANK'
    AND IS_IDENTITY = 'YES';

    After running the script, use Ctrl-A in the SQL Results window to hightlight all rows in the result set. Right click and select Copy rows from the pop up menu. Paste the rows in same or new SQL Script. Below is a snippet from the result set:

    ALTER TABLE DCRANK.EMP_ADDRESS ALTER COLUMN EMP_ADDR_PK SET GENERATED BY DEFAULT;
    ALTER TABLE DCRANK.EMP_MAST ALTER COLUMN EMP_MAST_PK SET GENERATED BY DEFAULT;
    ALTER TABLE DCRANK.EMPLOYEE_BIO ALTER COLUMN EMPLOYEE_BIO_PK SET GENERATED BY DEFAULT;

    Add a COMMIT statement after the last ALTER and the run the script and validate the changes.

    SELECT column_name, identity_generation
    FROM QSYS2.SYSCOLUMNS WHERE TABLE_SCHEMA = 'DCRANK'
    AND IS_IDENTITY = 'YES';

    EMP_ADDR_PK BY DEFAULT
    EMP_MAST_PK BY DEFAULT
    EMPLOYEE_BIO_PK BY DEFAULT

    You could also create an SQL Stored Procedure that is called from your CREATE TABLE scripts to alter the new tables just created. This procedure could return the above query as a result set.

    I would also like to see this as an option for the column in the PDM.

    Hope this helps.
    • R6BN_Robert_Wright
      R6BN_Robert_Wright
      13 Posts
      ACCEPTED ANSWER

      Re: Creating a Physical model for DB2: Editing Generate Type to DEFAULT?

      ‏2012-06-28T08:07:29Z  in response to DRCruikshank
      Although this is a way around this problem, it should be possible to specify in Data Architect whether or not the default value for an identity column is GENERATED ALWAYS or GENERATED BY DEFAULT.

      ER/Studio allows you to edit the DDL template used in generating models. Is there similar functionality in Data Architect for setting the template for the DDL?
  • DRCruikshank
    DRCruikshank
    6 Posts
    ACCEPTED ANSWER

    Re: Creating a Physical model for DB2: Editing Generate Type to DEFAULT?

    ‏2012-06-28T13:19:48Z  in response to SystemAdmin
    I am using IDA 8.1 and this version does provide an option for generating by default. Click on the identity column and choose Type from the Properties view

    .
    • R6BN_Robert_Wright
      R6BN_Robert_Wright
      13 Posts
      ACCEPTED ANSWER

      Re: Creating a Physical model for DB2: Editing Generate Type to DEFAULT?

      ‏2012-07-04T23:47:15Z  in response to DRCruikshank
      Thanks - it's the same in 7.5.3.

      I mean as an option for Transform To Physical - that way all Identity Columns can automatically be assigned the GENERATED BY DEFAULT value. With 200+ objects with Identity, it really is a lot of work to go through each object to set this attribute.
  • DRCruikshank
    DRCruikshank
    6 Posts
    ACCEPTED ANSWER

    Re: Creating a Physical model for DB2: Editing Generate Type to DEFAULT?

    ‏2012-07-05T13:08:00Z  in response to SystemAdmin
    Thanks for the clarification. I agree, it would be nice to be able to override this, perhaps as part of the DB2 Options under preferences.