Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2003
    Posts
    49

    Unanswered: DB2 Command Centre

    Hi,

    Since I am new to DB2, I am facing lot of problems.

    When I type

    begin atomic
    create table test1(col1 integer);
    end

    gives me an error

    SQL0104N An unexpected token "test1" was found following
    "atomic
    CREATE TABLE". Expected tokens may include: "<space>
    ".

    Explanation:

    A syntax error in the SQL statement was detected at the specified
    token following the text "<text>". The "<text>" field indicates
    the 20 characters of the SQL statement that preceded the token
    that is not valid.

    As an aid to the programmer, a partial list of valid tokens is
    provided in the SQLERRM field of the SQLCA as "<token-list>".
    This list assumes the statement is correct to that point.

    The statement cannot be processed.

    User Response:

    Examine and correct the statement in the area of the specified
    token.

    sqlcode : -104

    sqlstate : 42601

    but insert statements

    begin atomic
    insert into test1 values(1,1);
    insert into test1 values(2,2);
    insert into test1 values(3,3);
    end

    work fine...


    Thanks in advance
    Asha

  2. #2
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    Asha,

    I have no direct answer to your problem, but there is a difference in the SQL type you are using. Using DDL (Data definition Language) like the 'create' statement differs from the DML (Data manipulation Language) like 'insert' . From examples I have the DDL part is outside the 'atomic' part and the DML inside. Possibly DDL will not work because DML type statements are expected ???
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  3. #3
    Join Date
    Feb 2003
    Posts
    49
    HI,

    I think u r right. But my problems are not for whether I need to put the create statements in Begin atomic or not.

    I have a problem which is more like DB2 understanding related.....

    if <<conditional statement>> then
    alter table test10 add constraint ppok primary key(col1);
    end if;

    Fails given a similar type of error as mentioned in the first post.

    Any help will be appreciated.
    Regards,
    Asha
    Originally posted by blom0344
    Asha,

    I have no direct answer to your problem, but there is a difference in the SQL type you are using. Using DDL (Data definition Language) like the 'create' statement differs from the DML (Data manipulation Language) like 'insert' . From examples I have the DDL part is outside the 'atomic' part and the DML inside. Possibly DDL will not work because DML type statements are expected ???

  4. #4
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    Why do you wrap the DDL in a conditional clause anyway? If you issue the command manually you can just as well just issue the DDL part itself:

    alter table test10 add constraint ppok primary key(col1);

    The condition 1=1 is always true, so as a condition it is useless.

    My previous response was against another post you made.......
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  5. #5
    Join Date
    Feb 2003
    Posts
    49
    Hi,

    Basically I would try to find out if the constraint ppok is existing in SYSIBM.SYSCONSTDEP. If it is not then create, so something like


    if (select count(*) from SYSIBM.SYSCONSTDEP where DCONSTNAME='ppok')=0 then
    alter table test10 add constraint ppok primary key(col1);
    end if;

    Regards,
    Asha
    Originally posted by blom0344
    Why do you wrap the DDL in a conditional clause anyway? If you issue the command manually you can just as well just issue the DDL part itself:

    alter table test10 add constraint ppok primary key(col1);

    The condition 1=1 is always true, so as a condition it is useless.

    My previous response was against another post you made.......

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Why not do the statement alter table anyway ?

    If it fails , ignore it ...

    Cheers

    Sathyaram

    Originally posted by asha192
    Hi,

    Basically I would try to find out if the constraint ppok is existing in SYSIBM.SYSCONSTDEP. If it is not then create, so something like


    if (select count(*) from SYSIBM.SYSCONSTDEP where DCONSTNAME='ppok')=0 then
    alter table test10 add constraint ppok primary key(col1);
    end if;

    Regards,
    Asha
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Sorry ... May be you are looking for some specific application/scenario /..


    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    Feb 2003
    Posts
    49
    Since these get executed at the client's place I do not want the script to throw any errors.

    Regards,
    Asha
    Originally posted by sathyaram_s
    Sorry ... May be you are looking for some specific application/scenario /..


    Sathyaram

  9. #9
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    A thought (pessimistic though....) ...

    What will happen, taking your example, if a constraint ppok is exisiting on the database, but with some other definition, say, a primary key with the columns col1,col2 ?

    If this happens, your application is likely to fail , which is disastrous ...

    (I say this because I have seen this happen)

    IMHO, your approach needs to be more foolproof ....

    Go back to the client and ask for their current database object definitions, like , the output of db2look ...

    Use that to build your scritps , which will be a definitive one ...

    And don't ever try to make a change to the client's live database structure without first fully testing it on your site .....

    Don't forget to have a back-out plan and of course , test it for the most likely scenarios ... If you are altering a table, say, adding a column, and you want to backout , there is no way but to drop and recreate it ... Easy it may sound, but recreating a table is one of the most painful tasks, depending on the object definitions. A list of tasks may include(not a definitive list) : export exisiting data,drop the refrential constraints, drop the table, create the table, create triggers, create views, create aliases, grant privileges on them, import the data , create refrential constraints, do SET INTEGRITY on the dependent tables , runstats, rebind packages

    If possible take a backup of the live database before you make any modifications to it and make a note of the timestamp (or better is to take a quiesce point) before making changes to the db , so you can roll forward to that point ...

    I know, most of the above steps are time consuming and pain to do , but you never know , when this will come in handy ...

    HTH

    Cheers

    Sathyaram



    Originally posted by asha192
    Since these get executed at the client's place I do not want the script to throw any errors.

    Regards,
    Asha
    Last edited by sathyaram_s; 06-25-03 at 09:14.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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