Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jan 2012
    Posts
    11

    Unanswered: Create type in db2 8.2

    Hi,

    I am new to db2. I have worked a bit upon db2 9 on WINORA server. And now shifted to db2 8.2 on AIX server.
    I am facing problem while creating user defined type in db2 8.2, which was not the case in db2 9.
    For e.g. in db2 9, I used -
    CREATE TYPE strArray as VARCHAR(20) ARRAY[100];

    While I come across a bit of change in command for creating type in db2 8.2, which I came to know from IBM's site itself, and here it is -
    CREATE DISTINCT TYPE strArray as VARCHAR(20) ARRAY[100];

    But it's not working out. I checked with Privileges (especially for CREATING TYPE) for my db2 user and db2 schema with this following information -

    Authorization
    The privilege set that is defined below must include at least one of the following:

    The CREATEIN privilege on the schema
    SYSADM or SYSCTRL authority
    The authorization ID that matches the schema name implicitly has the CREATEIN privilege on the schema.

    Privilege set: If the statement is embedded in an application program, the privilege set is the privileges that are held by the authorization ID of the owner of the plan or package.

    If the statement is dynamically prepared, the privilege set is the privileges that are held by the SQL authorization ID of the process. The specified distinct type name can include a schema name (a qualifier). However, if the schema name is not the same as the SQL authorization ID, one of the following conditions must be met:

    The privilege set includes SYSADM or SYSCTRL authority.
    The SQL authorization ID of the process has the CREATEIN privilege on the schema.


    After giving these required privileges.. Still I am stuck up with the same problem in db2 8.2 with the following error, which is perfectly working on db2 9..

    ERROR: A character, token, or clause is invalid or missing.

    DB2
    SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=CREATE
    or replace TYPE;BEGIN-OF-STATEMENT;<create_type>, DRIVER=3.57.82
    Error
    Code: -104


    I would highly appreciate if someone helps me out for solving my problem.

    Thanks,
    Prodigy 201
    Last edited by prodigy201; 01-19-12 at 04:04.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    maybe cut and paste error
    CREATE DISTICNT TYPE --> CREATE DISTINCT TYPE
    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

  3. #3
    Join Date
    Jan 2012
    Posts
    11
    It was typing mistake over here :-) but it was correct while executing the command on server.

  4. #4
    Join Date
    Jan 2012
    Posts
    11
    Can anybody please help me to solve my problem?

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Array datatype is not supported on DB2 8.2 for LUW.

    CREATE TYPE (Array) was supported from DB2 9.5 for LUW, by referencing some PDF manuals.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    According to the error message, you are trying to use "OR REPLACE" option on the create statement, which is not evident from the "actual" statements you have shown. "OR REPLACE" was not available until DB2 9.7. You should read manuals for your specific version of DB2.

  7. #7
    Join Date
    Jan 2012
    Posts
    11
    Apart from arrays, it is also impossible to create simple user defined type of type varchar, too.. like -

    create distinct type person as varchar(100);

    And array type was not introduced in db2 8.2, is there any other way to handle this problem?

    Or, if I I take all values in varchar as comma separated and want to split it in stored procedure, is there any function to split varchar value into array. For e.g.

    "name1, name2, name3, name4, name5" can be split into varchar array?

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Or, if I I take all values in varchar as comma separated and want to split it ...
    Create another table to store variable number of "values".
    So, handling(insert/update/delete/select) the values will be much easier than storing all values in a varchar.

  9. #9
    Join Date
    Jan 2012
    Posts
    11
    Actually, I want to pass an array from java code to stored procedure as input parameter in stored procedure.

    I want to check if each element in that array is already there in the desired table. If the elements are not in the table, I would like to insert these elements into the table.

    If array was not introduced in db2 8.2, is there any way to handle array type in SP? Else, can we pass a string having all elements comma-separated as input parameter in stored procedure? And how to split then this string to get the all values as elements?

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I want to check if each element in that array is already there in the desired table. If the elements are not in the table, I would like to insert these elements into the table.
    I thought that stored procedure is not necessary to do that requirements.
    (If used a stored procedure, same complexity of code would be neccesary to construct a parameter for the procedure.)

    Construct an INSERT statement into a string variable from the array in Java, then execute it dynamicaly.

    The INSERT statement might look like...
    Code:
    INSERT INTO
     person_names(name)
    SELECT new_name
     FROM  (VALUES
                'name_1'
              , 'name_2'
              ...
              , 'name_n'
           ) t(new_name)
     WHERE new_name NOT IN (
           SELECT name
            FROM  person_names
           )
    ;
    Note: New lines and leading blanks in the sample code are for readability.
    They are not necessary in an actual statement string(one blank to separate words is enough).
    Last edited by tonkuma; 01-25-12 at 08:23. Reason: Add quoted statements(requirements) and some explanations.

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another issue is how you populated the values in the array.

    Depending on the way to populate the array,
    you might want to construct the INSERT statement directly without using intermediate array.

  12. #12
    Join Date
    Jan 2012
    Posts
    11
    Hi, that query you provided has worked out. Thanks a lot! :-)
    But if I want to take for more than one fields how can I right for multiple fields?

  13. #13
    Join Date
    Jan 2012
    Posts
    11
    INSERT INTO
    person_names(name)
    SELECT new_name
    FROM (VALUES
    'name_1'
    , 'name_2'
    ...
    , 'name_n'
    ) t(new_name)
    WHERE new_name NOT IN (
    SELECT name
    FROM person_names
    );
    Hi, that query you provided has worked out. Thanks a lot! :-)
    But if I want to take for more than one fields how can I write for multiple fields?

  14. #14
    Join Date
    Jan 2012
    Posts
    11
    Quote Originally Posted by tonkuma View Post
    Another issue is how you populated the values in the array.

    Depending on the way to populate the array,
    you might want to construct the INSERT statement directly without using intermediate array.
    I am taking comma separated values in String variable or Integer object to pass the values in query.
    Like -
    String str = new String('val1, 'val2', ........, 'val_n');

  15. #15
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I am taking comma separated values in String variable or Integer object to pass the values in query.
    Like -
    String str = new String('val1, 'val2', ........, 'val_n');
    I don't know Java.
    What value was in str, after the assignment?

    And, to what query do you want to pass the values?


    By the way,
    from where came val1, val2, ..., val_n?
    We might be better to go back more, to apply the following consideration.
    Quote Originally Posted by tonkuma View Post
    Another issue is how you populated the values in the array.

    Depending on the way to populate the array,
    you might want to construct the INSERT statement directly without using intermediate array.

Posting Permissions

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