Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2007
    Posts
    4

    Exclamation Unanswered: looking for DB2 function equivalent to INSTR() in Oracle (was "Please Help")

    I am looking for the funtion in Db2 which is equivalent to INSTR() funtion
    in Oracle.So far i have looked at funtions "LOCATE" and "POSSTR" in DB2
    which provide similar but LESS funtionality than INSTR() in Oracle.I am
    using DB2 8.0 .Any help will be greatly apprecitaed.

    the INSTR() funtion in Oracle does the following thing ----

    instr (string1, string2, [start_position], [nth_appearance])

    string1 is the string to search.

    string2 is the substring to search for in string1.

    start_position is the position in string1 where the search will start. This
    argument is optional. If omitted, it defaults to 1. The first position in
    the string is 1. If the start_position is negative, the function counts back
    start_position number of characters from the end of string1 and then
    searches towards the beginning of string1.

    nth_appearance is the nth appearance of string2. This is optional. If
    omiited, it defaults to 1.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    There is no equivalent built in function in DB2 for LUW.

    Andy

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The only missing pieces are the "n-th appearance" and negative start positions... You can easily implement both in a SQL-bodied function or recursive query.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Jun 2007
    Posts
    4
    Thank u. I used cast(?,varchar(255)) and it worked.

  5. #5
    Join Date
    Jun 2007
    Posts
    4

    Red face Help required

    one of the column in table called reports in db2 database was changed from organization_sequence_bigint to organization_sequence_number.
    therfore the queries which access this table reports is giving the following error.

    10:03:03 [SELECT - 0 row(s), 1.371 secs] [Error Code: -668, SQL State: 57016] Operation not allowed for reason code "7" on table "DASUSR1.REPORTS".

    What is the problem?

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Have a look at error message SQL0668, which reads:
    $ db2 "? sql668"

    SQL0668N Operation not allowed for reason code
    "<reason-code>" on table "<table-name>".

    Explanation:

    Access to table "<table-name>" is restricted. The cause is based
    on the following reason codes "<reason-code>":

    7 The table is in the reorg pending state. This can occur after
    an ALTER TABLE statement containing a REORG-recommended
    operation.

    User Response:

    7 Reorganize the table using the REORG TABLE command (note that
    INPLACE REORG TABLE is not allowed for a table that is in the
    reorg pending state).

    sqlcode : -668

    sqlstate : 57007
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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