Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2015
    Posts
    3

    Question Unanswered: Processing A LARGE Table Sequentially using DB2 on the IBM I

    I am trying to pull information for specific transactions from our FedEx server transmission file on the IBM I. The record length is 32,766 characters and there can be several hundreds of thousands of records on the file. I need to pull specific shipping transactions and the records must be read in the right sequence or the data will be interpreted improperly. I am using the following to ensure the records are processed sequentially:

    exec sql declare web cursor for select
    substr(webapit,1,1000), rrn(webapit) from webapit
    where substr(webapit,1,100) like :webshipmentid
    order by rrn(webapit);

    As the file grows, the response time gets worse. There are several users that will be accessing this program and all of them will be trying to re-arrange this huge file by rrn. I need another option. Can anyone provide a faster solution? Note: I am running the sql embedded in an RPG program.

    Thank you

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    You don't mention which version of DB2. Regardless I doubt you can create index on expression on the level you are on. I would question your where clause and indexing.

    First I would ensure an index on the column webapit

    Second why are you using a substr starting in position 1 in the where clause when you have a LIKE predicate? Should be written as where webapit like :webshipmentid so that you get index access.
    Dave

  3. #3
    Join Date
    Jan 2015
    Posts
    3
    Dave,

    I am using a substring because the only field in the file is also called webapit and it is 32,766 characters long. I know the "key" I am looking for should be within the first 100 characters.

    At any rate, I found a simple solution to my problem so I am all set. Thank you for your reply.

Posting Permissions

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