Weird SQL problem - IBM AS400

This is a discussion on Weird SQL problem - IBM AS400 ; Subject machine is a 9406-550 running V5R4M0. Level 8057 of SF99540, Level 15 of SF99504. In the last few days I've had a data transfer from iSeries program that is run as part of a batch file start to fail ...

+ Reply to Thread
Results 1 to 2 of 2

Thread: Weird SQL problem

  1. Weird SQL problem

    Subject machine is a 9406-550 running V5R4M0. Level 8057 of SF99540,
    Level 15 of SF99504.

    In the last few days I've had a data transfer from iSeries program
    that is run as part of a batch file start to fail on me. This has
    worked flawlessly for months. I found the problem, a column in one of
    the tables called "SCHOOLID" has one row where the value is non-
    numeric. The column is defined as character in the table. The job log
    for the transfer says that a data type conversion failed, error type
    6. The job log on the green screen shows the underlying SQL
    interpreter trying to cast the column "SCHOOLID" as a number, but it
    is a character column(!) and does not always have numeric values in
    it.

    There is nothing special about this transfer, the DTF file is not
    running an SQL statement, it merely selects based on the contents of
    the schoolid column and pulls every column from the table. Even if I
    set the output type at the data transfer from iseries prompt to
    "display" the transfer fails. If I delete the row where SCHOOLID is a
    non-numeric value, everything works fine. The only other thing I could
    think of is looking at the indexes on that table, which there was one
    so I deleted it. No change.

    Anyone else have this problem? I've got a PMR open at IBM now and
    after sending a few screen captures to the guy I'm working with I
    haven't heard anything. It may be in the hands of a developer now....

    Chris

  2. Re: Weird SQL problem

    Hal wrote:
    > Subject machine is a 9406-550 running V5R4M0. Level 8057 of SF99540,
    > Level 15 of SF99504.
    >
    > In the last few days I've had a data transfer from iSeries program
    > that is run as part of a batch file start to fail on me. This has
    > worked flawlessly for months. I found the problem, a column in one of
    > the tables called "SCHOOLID" has one row where the value is non-
    > numeric. The column is defined as character in the table. The job log
    > for the transfer says that a data type conversion failed, error type
    > 6. The job log on the green screen shows the underlying SQL
    > interpreter trying to cast the column "SCHOOLID" as a number, but it
    > is a character column(!) and does not always have numeric values in
    > it.
    >
    > There is nothing special about this transfer, the DTF file is not
    > running an SQL statement, it merely selects based on the contents of
    > the schoolid column and pulls every column from the table. Even if I
    > set the output type at the data transfer from iseries prompt to
    > "display" the transfer fails. If I delete the row where SCHOOLID is a
    > non-numeric value, everything works fine. The only other thing I could
    > think of is looking at the indexes on that table, which there was one
    > so I deleted it. No change.
    >
    > Anyone else have this problem? I've got a PMR open at IBM now and
    > after sending a few screen captures to the guy I'm working with I
    > haven't heard anything. It may be in the hands of a developer now....


    Your data transfer is most likely being executed as SQL on the iSeries,
    even though you didn't code any SQL. You can try to fix the SQL yourself.

    Open your data transfer, and click File + Properties. Click on the SQL
    tab, and change the options to specify "Process SELECT as native SQL".
    Click OK. Now click the Data Options button on your main DTF screen.
    You should now see the SQL statement. If it indicates SELECT *, you'll
    need to replace the * with the actual column names (with luck, there
    aren't a lot.) For the SCHOOLID column, you'll probably want to code a
    CASE function:

    select col1, col2, col3, ...
    case when schoolid between '000' and '999'
    then schoolid
    else
    '000'
    end,
    colx, coly, colz ...
    from mylib/mytable
    where [blah blah blah]

    Then click OK.

    I made an assumption above that schoolid can contain up to three digits;
    change it to fit your actual situation.

    As for that index you deleted...put it back! Indexes are crucial for
    performance when your queries have WHERE criteria specified.

+ Reply to Thread