I am trying to use db2batch from my XP client against a remote
database.
And here's what I am getting:

C:\work\Tuning\DB2\db2batch> db2batch -d oakl0 -a fs/fs -f
db2batch.sql
Running in Embedded Dynamic mode.

---------------------------------------------
--#SET PERF_DETAIL 5

Statement number: 1

select count(*) from files

1
--------------
2715211


Number of rows retrieved is: 1
Number of rows sent to output is: 1

SQL1427N An instance attachment does not exist.


Elapsed Time is: 1.772 seconds

---------------------------------------------



Summary of Results
==================
Elapsed Agent CPU Rows Rows
Statement # Time (s) Time (s) Fetched
Printed
1 1.772 Not Collected 1 1

Arith. mean 1.772
Geom. mean 1.772


I tried to attach before executing db2batch like
C:\work\Tuning\DB2\db2batch> db2 attach to linux0 user fs using fs

Instance Attachment Information

Instance server = DB2/LINUX 8.1.3
Authorization ID = FS
Local instance alias = LINUX0

but it didn't help.

The same procedure works fine localy on the server generating stats
according to the PERF_DETAIL set.

Any idea?

Thanks
-Eugene

"Eugene" <eugene@profitlogic.com> wrote in message
news:9573e6b2.0312030716.1c86a084@posting.google.c om...
> I am trying to use db2batch from my XP client against a remote
> database.
> And here's what I am getting:
>
> [snip]
> The same procedure works fine localy on the server generating stats
> according to the PERF_DETAIL set.
>
> Any idea?
>
> Thanks
> -Eugene

Try changing the default DB2 instance. Issue the following command from your
XP command window:

db2set db2instance=new_value

That is strange, but db2set fails to set DB2INSTANCE:

C:\Program Files\IBM\SQLLIB\BIN>db2set DB2INSTANCE=linux0

DBI1302E Invalid parameter detected.

Explanation:

An invalid parameter was used.

User Response:

Use the -? option for the usage help message.

Well... then I tried to reset DB2INSTDEF from the default, ie local
DB2, to the remote one on linux:

C:\Program Files\IBM\SQLLIB\BIN>db2set -all
[e] DB2PATH=C:\Program Files\IBM\SQLLIB
[i] DB2_CORRELATED_PREDICATES=TRUE
[i] DB2_HASH_JOIN=YES
[i] DB2ACCOUNTNAME=LIZARD\efiryago
[i] DB2INSTOWNER=LIZARD
[i] DB2PORTRANGE=60000:60003
[i] DB2_GRP_LOOKUP=LOCAL
[i] DB2INSTPROF=C:\PROGRA~1\IBM\SQLLIB
[i] DB2COMM=TCPIP
[g] DB2_DOCCDPATH=C:\Program Files\IBM\SQLLIB\
[g] DB2SYSTEM=LIZARD
[g] DB2PATH=C:\Program Files\IBM\SQLLIB
[g] DB2INSTDEF=DB2
[g] DB2ADMINSERVER=DB2DAS00

C:\Program Files\IBM\SQLLIB\BIN>db2set DB2INSTDEF=linux0

C:\Program Files\IBM\SQLLIB\BIN>db2set -all
[e] DB2PATH=C:\Program Files\IBM\SQLLIB
[i] DB2_CORRELATED_PREDICATES=TRUE
[i] DB2_HASH_JOIN=YES
[i] DB2ACCOUNTNAME=LIZARD\efiryago
[i] DB2INSTOWNER=LIZARD
[i] DB2PORTRANGE=60000:60003
[i] DB2_GRP_LOOKUP=LOCAL
[i] DB2INSTPROF=C:\PROGRA~1\IBM\SQLLIB
[i] DB2COMM=TCPIP
[g] DB2_DOCCDPATH=C:\Program Files\IBM\SQLLIB\
[g] DB2SYSTEM=LIZARD
[g] DB2PATH=C:\Program Files\IBM\SQLLIB
[g] DB2INSTDEF=LINUX0
[g] DB2ADMINSERVER=DB2DAS00


C:\work\Tuning\DB2\db2batch>db2 list db directory

System Database Directory

Number of entries in the directory = 8
....
Database 4 entry:

Database alias = OAKL0
Database name = OAKDB
Node name = LINUX0
Database release level = a.00
Comment =
Directory entry type = Remote
Catalog database partition number = -1
....


No any better:

C:\work\Tuning\DB2\db2batch>db2batch -d oakl0 -a fs/fs -f db2batch.sql
Running in Embedded Dynamic mode.

---------------------------------------------
--#SET PERF_DETAIL 5

Statement number: 1

select count(*) from files

1
--------------
2715211


Number of rows retrieved is: 1
Number of rows sent to output is: 1

SQL1427N An instance attachment does not exist.


Elapsed Time is: 2.243 seconds

---------------------------------------------



Summary of Results
==================
Elapsed Agent CPU Rows Rows
Statement # Time (s) Time (s) Fetched
Printed
1 2.243 Not Collected 1 1

Arith. mean 2.243
Geom. mean 2.243


By the way, I've been told, db2batch from 8.1GA client on W2K works
fine against a remote database, bug in FP3?

-Eugene


"Mark A" <ma@switchboard.net> wrote in message news:<Grnzb.832$Jf4.20107@news.uswest.net>...
> "Eugene" <eugene@profitlogic.com> wrote in message
> news:9573e6b2.0312030716.1c86a084@posting.google.c om...
> > I am trying to use db2batch from my XP client against a remote
> > database.
> > And here's what I am getting:
> >
> > [snip]
> > The same procedure works fine localy on the server generating stats
> > according to the PERF_DETAIL set.
> >
> > Any idea?
> >
> > Thanks
> > -Eugene
>
> Try changing the default DB2 instance. Issue the following command from your
> XP command window:
>
> db2set db2instance=new_value

Eugene wrote:
> That is strange, but db2set fails to set DB2INSTANCE:
>
> C:\Program Files\IBM\SQLLIB\BIN>db2set DB2INSTANCE=linux0
>
I would use SET DB2INSTANCE=name as it is environment variable to be
set, not DB2 regisry variable.

On Windows:

My Computer (right mouse click) -> Properties -> Advanced Tab ->
Environment Variables

Top pane is for the User variables of the currently logged on user;
bottom pane is for system variables. Use bottom pane.

After changes are done close the existin open windows on desktop and
open then again.

You may verify your settings by issuing from command prompt:

set | find "DB2INSTANCE"

If you need to change your environment variable *just* for current
session, just issue:

set DB2INSTANCE=newinst

This is valid until end of this session (until you close the window).


Jan M. Nelken

Hi Jan,

After I reset the XP system environment variable DB2INSTANCE from the
default DB2 to the new remote one LINIX0, I am getting this:

[db2batch]$ db2batch -d oakl0 -a fs/fs -f db2batch.sql

ERROR!!! -> Could not connect to database, SQLCODE = -1390
Make sure database name is correct
Type "db2batch -h" for more help

And, even worst, the DB2 client stopped working with the message:

C:\Program Files\IBM\SQLLIB\BIN>db2
SQL1390C The environment variable DB2INSTANCE is not defined or is
invalid (?)

Then, I also tried to reset DB2INSTDEF to LINIX0 by db2set so that
DB2INSTDEF would match DB2INSTANCE (just as a try)

C:\Program Files\IBM\SQLLIB\BIN>db2set -all
[e] DB2PATH=C:\Program Files\IBM\SQLLIB
[g] DB2_DOCCDPATH=C:\Program Files\IBM\SQLLIB\
[g] DB2SYSTEM=LIZARD
[g] DB2PATH=C:\Program Files\IBM\SQLLIB,
[g] DB2INSTDEF=LINUX0
[g] DB2ADMINSERVER=DB2DAS00


but it didn't help. So I had to reset both vars back to the
installation default, ie DB2, to make the client working at all.

Finaly, I opened pmr with IBM support )
However, any further idea from the community on that would be
appreciated!

Thanks,
-Eugene





"Jan M. Nelken" <Unknown.User@Invalid.Domain> wrote in message news:<3fcf50c2_4@news1.prserv.net>...
> Eugene wrote:
> > That is strange, but db2set fails to set DB2INSTANCE:
> >
> > C:\Program Files\IBM\SQLLIB\BIN>db2set DB2INSTANCE=linux0
> >
> I would use SET DB2INSTANCE=name as it is environment variable to be
> set, not DB2 regisry variable.
>
> On Windows:
>
> My Computer (right mouse click) -> Properties -> Advanced Tab ->
> Environment Variables
>
> Top pane is for the User variables of the currently logged on user;
> bottom pane is for system variables. Use bottom pane.
>
> After changes are done close the existin open windows on desktop and
> open then again.
>
> You may verify your settings by issuing from command prompt:
>
> set | find "DB2INSTANCE"
>
> If you need to change your environment variable *just* for current
> session, just issue:
>
> set DB2INSTANCE=newinst
>
> This is valid until end of this session (until you close the window).
>
>
> Jan M. Nelken

Set the db2instance and db2instdef back to the original setting. Then
catalog the remote node and remote database then just connect to it

--
Garfield A. Lewis
DB2 UDB Development,
IBM Canada Laboratory


"Eugene" <eugene@profitlogic.com> wrote in message
news:9573e6b2.0312080950.3318a7c8@posting.google.c om...
> Hi Jan,
>
> After I reset the XP system environment variable DB2INSTANCE from the
> default DB2 to the new remote one LINIX0, I am getting this:
>
> [db2batch]$ db2batch -d oakl0 -a fs/fs -f db2batch.sql
>
> ERROR!!! -> Could not connect to database, SQLCODE = -1390
> Make sure database name is correct
> Type "db2batch -h" for more help
>
> And, even worst, the DB2 client stopped working with the message:
>
> C:\Program Files\IBM\SQLLIB\BIN>db2
> SQL1390C The environment variable DB2INSTANCE is not defined or is
> invalid (?)
>
> Then, I also tried to reset DB2INSTDEF to LINIX0 by db2set so that
> DB2INSTDEF would match DB2INSTANCE (just as a try)
>
> C:\Program Files\IBM\SQLLIB\BIN>db2set -all
> [e] DB2PATH=C:\Program Files\IBM\SQLLIB
> [g] DB2_DOCCDPATH=C:\Program Files\IBM\SQLLIB\
> [g] DB2SYSTEM=LIZARD
> [g] DB2PATH=C:\Program Files\IBM\SQLLIB,
> [g] DB2INSTDEF=LINUX0
> [g] DB2ADMINSERVER=DB2DAS00
>
>
> but it didn't help. So I had to reset both vars back to the
> installation default, ie DB2, to make the client working at all.
>
> Finaly, I opened pmr with IBM support )
> However, any further idea from the community on that would be
> appreciated!
>
> Thanks,
> -Eugene
>
>
>
>
>
> "Jan M. Nelken" <Unknown.User@Invalid.Domain> wrote in message
news:<3fcf50c2_4@news1.prserv.net>...
> > Eugene wrote:
> > > That is strange, but db2set fails to set DB2INSTANCE:
> > >
> > > C:\Program Files\IBM\SQLLIB\BIN>db2set DB2INSTANCE=linux0
> > >
> > I would use SET DB2INSTANCE=name as it is environment variable to be
> > set, not DB2 regisry variable.
> >
> > On Windows:
> >
> > My Computer (right mouse click) -> Properties -> Advanced Tab ->
> > Environment Variables
> >
> > Top pane is for the User variables of the currently logged on user;
> > bottom pane is for system variables. Use bottom pane.
> >
> > After changes are done close the existin open windows on desktop and
> > open then again.
> >
> > You may verify your settings by issuing from command prompt:
> >
> > set | find "DB2INSTANCE"
> >
> > If you need to change your environment variable *just* for current
> > session, just issue:
> >
> > set DB2INSTANCE=newinst
> >
> > This is valid until end of this session (until you close the window).
> >
> >
> > Jan M. Nelken

In article <9573e6b2.0312030716.1c86a084@posting.google.com >,
eugene@profitlogic.com says...
> I am trying to use db2batch from my XP client against a remote
> database.
> And here's what I am getting:
>
> C:\work\Tuning\DB2\db2batch> db2batch -d oakl0 -a fs/fs -f
> db2batch.sql
> Running in Embedded Dynamic mode.
>
> ---------------------------------------------
> --#SET PERF_DETAIL 5
>
> Statement number: 1
>
> select count(*) from files
>
> 1
> --------------
> 2715211
>
>
> Number of rows retrieved is: 1
> Number of rows sent to output is: 1
>
> SQL1427N An instance attachment does not exist.
>
>
> Elapsed Time is: 1.772 seconds
>
> ---------------------------------------------
>
>
>
> Summary of Results
> ==================
> Elapsed Agent CPU Rows Rows
> Statement # Time (s) Time (s) Fetched
> Printed
> 1 1.772 Not Collected 1 1
>
> Arith. mean 1.772
> Geom. mean 1.772
>
>
> I tried to attach before executing db2batch like
> C:\work\Tuning\DB2\db2batch> db2 attach to linux0 user fs using fs
>
> Instance Attachment Information
>
> Instance server = DB2/LINUX 8.1.3
> Authorization ID = FS
> Local instance alias = LINUX0
>
> but it didn't help.
>
> The same procedure works fine localy on the server generating stats
> according to the PERF_DETAIL set.
>
> Any idea?
>
> Thanks
> -Eugene
>


It looks like db2batch attaches to the remote instance without using
the specified user/pasword. Does it work if you specify another
level of PERF_DETAIL ?

I tried and it, of course, cusseeds for PERF_DETAIL=0,1 which do not
generate detailed SQL stats, but does not for greater 3,4,5 as desired

-Eugene


Gert van der Kooij <gert@invalid.nl> wrote in message news:<MPG.1a3ee8bf276095a9896b1@news.xs4all.nl>...
> In article <9573e6b2.0312030716.1c86a084@posting.google.com >,
> eugene@profitlogic.com says...
> > I am trying to use db2batch from my XP client against a remote
> > database.
> > And here's what I am getting:
> >
> > C:\work\Tuning\DB2\db2batch> db2batch -d oakl0 -a fs/fs -f
> > db2batch.sql
> > Running in Embedded Dynamic mode.
> >
> > ---------------------------------------------
> > --#SET PERF_DETAIL 5
> >
> > Statement number: 1
> >
> > select count(*) from files
> >
> > 1
> > --------------
> > 2715211
> >
> >
> > Number of rows retrieved is: 1
> > Number of rows sent to output is: 1
> >
> > SQL1427N An instance attachment does not exist.
> >
> >
> > Elapsed Time is: 1.772 seconds
> >
> > ---------------------------------------------
> >
> >
> >
> > Summary of Results
> > ==================
> > Elapsed Agent CPU Rows Rows
> > Statement # Time (s) Time (s) Fetched
> > Printed
> > 1 1.772 Not Collected 1 1
> >
> > Arith. mean 1.772
> > Geom. mean 1.772
> >
> >
> > I tried to attach before executing db2batch like
> > C:\work\Tuning\DB2\db2batch> db2 attach to linux0 user fs using fs
> >
> > Instance Attachment Information
> >
> > Instance server = DB2/LINUX 8.1.3
> > Authorization ID = FS
> > Local instance alias = LINUX0
> >
> > but it didn't help.
> >
> > The same procedure works fine localy on the server generating stats
> > according to the PERF_DETAIL set.
> >
> > Any idea?
> >
> > Thanks
> > -Eugene
> >
>
>
> It looks like db2batch attaches to the remote instance without using
> the specified user/pasword. Does it work if you specify another
> level of PERF_DETAIL ?

In article <9573e6b2.0312081429.39535f7c@posting.google.com >,
eugene@profitlogic.com says...
> I tried and it, of course, cusseeds for PERF_DETAIL=0,1 which do not
> generate detailed SQL stats, but does not for greater 3,4,5 as desired
>

Which indeed indicates that the instance attachment doesn't use the
specified user/password. It looks like a bug to me.

Connect from what?
Before I first tried db2batch, I had the remote instance and database
cataloged and accessed from other apps like CLP on my XP client.

"Garfield Lewis" <galewis@SPAM-NO-MOREca.ibm.com> wrote in message news:<br2fq4$agh$1@hanover.torolab.ibm.com>...
> Set the db2instance and db2instdef back to the original setting. Then
> catalog the remote node and remote database then just connect to it
>
> --
> Garfield A. Lewis
> DB2 UDB Development,
> IBM Canada Laboratory
>
>
> "Eugene" <eugene@profitlogic.com> wrote in message
> news:9573e6b2.0312080950.3318a7c8@posting.google.c om...
> > Hi Jan,
> >
> > After I reset the XP system environment variable DB2INSTANCE from the
> > default DB2 to the new remote one LINIX0, I am getting this:
> >
> > [db2batch]$ db2batch -d oakl0 -a fs/fs -f db2batch.sql
> >
> > ERROR!!! -> Could not connect to database, SQLCODE = -1390
> > Make sure database name is correct
> > Type "db2batch -h" for more help
> >
> > And, even worst, the DB2 client stopped working with the message:
> >
> > C:\Program Files\IBM\SQLLIB\BIN>db2
> > SQL1390C The environment variable DB2INSTANCE is not defined or is
> > invalid (?)
> >
> > Then, I also tried to reset DB2INSTDEF to LINIX0 by db2set so that
> > DB2INSTDEF would match DB2INSTANCE (just as a try)
> >
> > C:\Program Files\IBM\SQLLIB\BIN>db2set -all
> > [e] DB2PATH=C:\Program Files\IBM\SQLLIB
> > [g] DB2_DOCCDPATH=C:\Program Files\IBM\SQLLIB\
> > [g] DB2SYSTEM=LIZARD
> > [g] DB2PATH=C:\Program Files\IBM\SQLLIB,
> > [g] DB2INSTDEF=LINUX0
> > [g] DB2ADMINSERVER=DB2DAS00
> >
> >
> > but it didn't help. So I had to reset both vars back to the
> > installation default, ie DB2, to make the client working at all.
> >
> > Finaly, I opened pmr with IBM support )
> > However, any further idea from the community on that would be
> > appreciated!
> >
> > Thanks,
> > -Eugene
> >
> >
> >
> >
> >
> > "Jan M. Nelken" <Unknown.User@Invalid.Domain> wrote in message
> news:<3fcf50c2_4@news1.prserv.net>...
> > > Eugene wrote:
> > > > That is strange, but db2set fails to set DB2INSTANCE:
> > > >
> > > > C:\Program Files\IBM\SQLLIB\BIN>db2set DB2INSTANCE=linux0
> > > >
> > > I would use SET DB2INSTANCE=name as it is environment variable to be
> > > set, not DB2 regisry variable.
> > >
> > > On Windows:
> > >
> > > My Computer (right mouse click) -> Properties -> Advanced Tab ->
> > > Environment Variables
> > >
> > > Top pane is for the User variables of the currently logged on user;
> > > bottom pane is for system variables. Use bottom pane.
> > >
> > > After changes are done close the existin open windows on desktop and
> > > open then again.
> > >
> > > You may verify your settings by issuing from command prompt:
> > >
> > > set | find "DB2INSTANCE"
> > >
> > > If you need to change your environment variable *just* for current
> > > session, just issue:
> > >
> > > set DB2INSTANCE=newinst
> > >
> > > This is valid until end of this session (until you close the window).
> > >
> > >
> > > Jan M. Nelken