Db2

Db2

Connect with Db2, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only
Back to discussions
Expand all | Collapse all

create table like

  • 1.  create table like

    Posted Wed January 06, 2021 05:41 AM
    i am using the statement create table like..     including defaults including identity column attributes but
    the generated always setting is missing

    "CREATED_BY" "META "."AUDIT_USER_TYPE" NOT NULL ,                          <-- like table
    "CREATED_BY" "META "."AUDIT_USER_TYPE" NOT NULL GENERATED ALWAYS AS ( CORE_DL.GETCURRENTUSER() ) ,  <--base table

    I could not find an option to force this
    Is this correct (as documented) or is there anyhow an option ?

    ------------------------------
    Thanks for all answers
    Best Regards,
    Guy Przytula
    ------------------------------

    #Db2


  • 2.  RE: create table like

    Posted Wed January 06, 2021 06:10 AM
    I noticed the same thing a few days ago.

    My source table was a federated nickname on Db2 for iSeries.

    I had thought it was a restriction of the fact I was going to iSeries.

    But your example suggests it isn't.

    It looks like a bug to me?

    I was using 11.5.5.0 if that is any use.

    Phil



    Original Message


  • 3.  RE: create table like

    Posted Wed January 06, 2021 09:53 AM
    I opened a case and this is the answer : waiting for feedback after escalate


    I've reviewed the documentation regarding the LIKE TABLE, its copy-options and GENERATED AS columns. There is nothing that indicates that GENERATED AS should not be copied. There are a few mentions that some tables in some specific circiumstances loose the generated columns status (when copying from a nickname), but it does not seem to apply for normal tables. Which suggests that the generated column status can and should be retained in a copy.

    So it would seem to be that it is a defect. And if it is not, then a clear mention should be made in the documentation. I'm going to escalate the issue to have a definitive answer. as for now, I'm afraid I don't have a WA for this issue.



    ------------------------------
    Thanks for all answers
    Best Regards,
    Guy Przytula
    ------------------------------

    Original Message


  • 4.  RE: create table like

    Posted Thu January 07, 2021 11:47 AM
    There is the following restriction in the LIKE clause when it comes to including column defaults. It says that columns that are not updateable will not have a default defined... If you were doing an insert into the table you would need to use the DEFAULT clause in the column list for the statement to work or else you would get an error because it is a generated column. That's my theory anyway!

    copy-options
    These options specify whether to copy additional attributes of the source result table definition (table, view, or fullselect). 
    INCLUDING COLUMN DEFAULTS
    Column defaults for each updatable column of the source result table definition are copied. Columns that are not updatable will not have a default defined in the corresponding column of the created table. 

    If LIKE table-name is specified and table-name identifies a base table, created temporary table, or declared temporary table, then INCLUDING COLUMN DEFAULTS is the default. If LIKE table-nameis specified and table-name identifies a nickname, then INCLUDING COLUMN DEFAULTS has no effect and column defaults are not copied.


    George

    George Baklarz




    Original Message


  • 5.  RE: create table like

    Posted Thu January 07, 2021 02:05 PM
    Thks for update, but the default clause is not the problem. The generated clause is the problem. 
    Best regards, Guy



    Original Message


  • 6.  RE: create table like

    Posted Thu January 07, 2021 02:10 AM
    Hi,

    If the specified table or view contains an identity column, you must specify the option INCLUDING IDENTITY on the CREATE TABLE statement if you want the identity column to exist in the new table. The default behavior for CREATE TABLE is EXCLUDING IDENTITY.

    Best Regards,
    Masheed Ullah



    Original Message


  • 7.  RE: create table like

    Posted Thu January 07, 2021 02:25 AM
    thanks for the update but : as indicated in first entry I use
    create table like.. including defaults including identity column attributes

    ------------------------------
    Thanks for all answers
    Best Regards,
    Guy Przytula
    ------------------------------

    Original Message


  • 8.  RE: create table like

    Posted Thu January 07, 2021 09:46 AM
    Hi,
     
    Try this command:
     
    db2 " create table newtab like existtab INCLUDING IDENTITY"
    DB20000I  The SQL command completed successfully.
     
    Masheed Ullah
    Database Administrator
    Phone: +92-304-6664916
     




    Original Message


  • 9.  RE: create table like

    Posted Thu January 07, 2021 10:02 AM
    I do not know why you are repeating this command as in the first entry I indicated this option and also the default option..

    ------------------------------
    Thanks for all answers
    Best Regards,
    Guy Przytula
    ------------------------------

    Original Message


  • 10.  RE: create table like

    Posted Thu January 07, 2021 05:15 AM
    Did that.   Doesn't work (in my case from a nickname on iSeries).

    Phil



    Original Message


  • 11.  RE: create table like

    Posted Thu January 07, 2021 10:05 AM
    in the knowledge center, there is a remark about nicknames
    also waiting for feedback from IBM..

    ------------------------------
    Thanks for all answers
    Best Regards,
    Guy Przytula
    ------------------------------

    Original Message


Related Content