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

    Unanswered: DB2 CLI Help Required

    Please tell me if you have good experience with DB2 CLI how i can improve the fetch rate from DB2 database?

  2. #2
    Join Date
    Jan 2012
    Posts
    53
    Nobody is working with DB2 CLI C++?

  3. #3
    Join Date
    Nov 2011
    Posts
    334
    How fast do you expect ?
    normally, the speed of db2 fetching data will reach at least 20W/s,
    in most case , this will not be the bottlenck in your whole data process.
    If you want to kown the exactly speed ,
    you can use db2batch to test it....
    Last edited by fengsun2; 01-11-12 at 04:22.

  4. #4
    Join Date
    Jan 2012
    Posts
    53
    I am fetching through CLI in C++ application.
    and getting 200MB/10min
    which is very slow.

  5. #5
    Join Date
    Jan 2012
    Posts
    53
    i have tried DB2HPU which is very best but i can't use this in C++.

  6. #6
    Join Date
    Aug 2011
    Posts
    46
    Quote Originally Posted by irfanmaroof View Post
    I am fetching through CLI in C++ application.
    and getting 200MB/10min
    which is very slow.
    since you can dump all the data pretty quick using high performance unload, I don't think problem is with server side.

    You can set RQRIOBLK parameter to higher value, see if that helps RQRIOBLK = 65535, default is 32k.

    Cursor processing is not going to be as fast as utilities.
    If you want to stick with CLI way, only thing I can think of is having multiple parallel threads in application.

  7. #7
    Join Date
    Jan 2012
    Posts
    53
    amit i have told this thing many time that application is not effecting the fetch process.I have tried this with sample application in which i am just fetching the records with out any other processing.but fetching is still slow.
    tell me the other possibilities to fetch data in C++ from DB2.

  8. #8
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    The least you could do is show us your db2cli.ini

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    We had another thread on this and the discussion was always centering around the fact that 20MB/min is very, very slow. This is not what DB2 CLI can deliver in normal situations (much less properly tuned systems). So you have either a problem in your application or a badly misconfigured system (e.g. CLI trace is active or so).

    Summarized: I have never encountered a situation where CLI turned out to be a performance bottleneck.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Jan 2012
    Posts
    53
    db2cli.ini


    ; Comment lines start with a semi-colon.

    [tstcli1x]
    uid=userid
    pwd=password
    autocommit=0
    TableType="'TABLE','VIEW','SYSTEM TABLE'"

    [tstcli2x]
    ; Assuming dbalias2 is a database in DB2 for MVS.
    SchemaList="'OWNER1','OWNER2',CURRENT SQLID"

    [MyVeryLongDBALIASName]
    dbalias=dbalias3
    SysSchema=MYSCHEMA

  11. #11
    Join Date
    Nov 2011
    Posts
    334
    Can you put the db2batch output here.If it's performance is also poor ,Maybe your system is misconfigred or There is a problem with your io system, maybe you must talk to storage guys.....
    Try this: db2batch -d <dbname> -i complete -f sqlfile -o p 5 e yes r 5
    in unix platform, use dd command to see the device io capabilities....

  12. #12
    Join Date
    Aug 2011
    Posts
    46
    Quote Originally Posted by irfanmaroof View Post
    amit i have told this thing many time that application is not effecting the fetch process.I have tried this with sample application in which i am just fetching the records with out any other processing.but fetching is still slow.
    tell me the other possibilities to fetch data in C++ from DB2.
    what I meant to say, break the fetch processing in couple of chunks and do parallel fetch. Here is a small test I did on my great desktop machine , which I know how powerful is, as I am getting 9MB/min.

    First, I ran with one thread doing the fetch, application took : 619 sec
    Second, two threads doing fetch paralleled, application took : 317 sec
    Third, three threads doing fetch paralleled, application took : 228 sec

    Almost linear scalability. Off course it depends on application design whether you can break the result set in such a way that all the threads can pick unique set and work on that.

  13. #13
    Join Date
    Jan 2012
    Posts
    1

    More info needed

    What is you column data type ? and what are the CLI APIs that you are using for the retrieval ?

    The db2cli.ini which you have pasted are the default ones and I could not see any important information there.

    Thanks and Regards,
    Premnath

  14. #14
    Join Date
    Jan 2012
    Posts
    53
    amit send me that code i want to run with my db and on my system.

  15. #15
    Join Date
    Jan 2012
    Posts
    53
    Quote Originally Posted by vasan_prem View Post
    What is you column data type ? and what are the CLI APIs that you are using for the retrieval ?

    The db2cli.ini which you have pasted are the default ones and I could not see any important information there.

    Thanks and Regards,
    Premnath

    column datatypes are all possible datatype from DB2.AND cli is API for DB2 to use with C++ development.I am using SQLFETCHSCROLL to get data in bulks.

    What attributes do you think i should add?

Posting Permissions

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