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

Nobody is working with DB2 CLI C++?

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....

I am fetching through CLI in C++ application.
and getting 200MB/10min
which is very slow.

i have tried DB2HPU which is very best but i can't use this in C++.

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.

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.

The least you could do is show us your db2cli.ini

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.

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

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....

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.

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

amit send me that code i want to run with my db and on my system.

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?