db2batch from windows client: SQL1427N An instance attachment does not exist

**Mark A** · Nov 12 '05, 05:58 AM

Re: db2batch from windows client: SQL1427N An instance attachment does not exist

"Eugene" <eugene@profitl ogic.com> wrote in message
news:9573e6b2.0 312030716.1c86a 084@posting.goo gle.com...[color=blue]
> 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[/color]

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

db2set db2instance=new _value

**Eugene** · Nov 12 '05, 05:59 AM

Re: db2batch from windows client: SQL1427N An instance attachment does not exist

That is strange, but db2set fails to set DB2INSTANCE:

C:\Program Files\IBM\SQLLI B\BIN>db2set DB2INSTANCE=lin ux0

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\SQLLI B\BIN>db2set -all
[e] DB2PATH=C:\Prog ram Files\IBM\SQLLI B
[i] DB2_CORRELATED_ PREDICATES=TRUE
[i] DB2_HASH_JOIN=Y ES
[i] DB2ACCOUNTNAME= LIZARD\efiryago
[i] DB2INSTOWNER=LI ZARD
[i] DB2PORTRANGE=60 000:60003
[i] DB2_GRP_LOOKUP= LOCAL
[i] DB2INSTPROF=C:\ PROGRA~1\IBM\SQ LLIB
[i] DB2COMM=TCPIP
[g] DB2_DOCCDPATH=C :\Program Files\IBM\SQLLI B\
[g] DB2SYSTEM=LIZAR D
[g] DB2PATH=C:\Prog ram Files\IBM\SQLLI B
[g] DB2INSTDEF=DB2
[g] DB2ADMINSERVER= DB2DAS00

C:\Program Files\IBM\SQLLI B\BIN>db2set DB2INSTDEF=linu x0

C:\Program Files\IBM\SQLLI B\BIN>db2set -all
[e] DB2PATH=C:\Prog ram Files\IBM\SQLLI B
[i] DB2_CORRELATED_ PREDICATES=TRUE
[i] DB2_HASH_JOIN=Y ES
[i] DB2ACCOUNTNAME= LIZARD\efiryago
[i] DB2INSTOWNER=LI ZARD
[i] DB2PORTRANGE=60 000:60003
[i] DB2_GRP_LOOKUP= LOCAL
[i] DB2INSTPROF=C:\ PROGRA~1\IBM\SQ LLIB
[i] DB2COMM=TCPIP
[g] DB2_DOCCDPATH=C :\Program Files\IBM\SQLLI B\
[g] DB2SYSTEM=LIZAR D
[g] DB2PATH=C:\Prog ram Files\IBM\SQLLI B
[g] DB2INSTDEF=LINU X0
[g] DB2ADMINSERVER= DB2DAS00

C:\work\Tuning\ DB2\db2batch>db 2 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>db 2batch -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>...[color=blue]
> "Eugene" <eugene@profitl ogic.com> wrote in message
> news:9573e6b2.0 312030716.1c86a 084@posting.goo gle.com...[color=green]
> > 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[/color]
>
> Try changing the default DB2 instance. Issue the following command from your
> XP command window:
>
> db2set db2instance=new _value[/color]

**Jan M. Nelken** · Nov 12 '05, 05:59 AM

Re: db2batch from windows client: SQL1427N An instance attachmentdoes not exist

Eugene wrote:[color=blue]
> That is strange, but db2set fails to set DB2INSTANCE:
>
> C:\Program Files\IBM\SQLLI B\BIN>db2set DB2INSTANCE=lin ux0
>[/color]
I would use SET DB2INSTANCE=nam e 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 "DB2INSTANC E"

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

set DB2INSTANCE=new inst

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

Jan M. Nelken

**Eugene** · Nov 12 '05, 06:01 AM

Re: db2batch from windows client: SQL1427N An instance attachment does not exist

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\SQLLI B\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\SQLLI B\BIN>db2set -all
[e] DB2PATH=C:\Prog ram Files\IBM\SQLLI B
[g] DB2_DOCCDPATH=C :\Program Files\IBM\SQLLI B\
[g] DB2SYSTEM=LIZAR D
[g] DB2PATH=C:\Prog ram Files\IBM\SQLLI B,
[g] DB2INSTDEF=LINU X0
[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@I nvalid.Domain> wrote in message news:<3fcf50c2_ 4@news1.prserv. net>...[color=blue]
> Eugene wrote:[color=green]
> > That is strange, but db2set fails to set DB2INSTANCE:
> >
> > C:\Program Files\IBM\SQLLI B\BIN>db2set DB2INSTANCE=lin ux0
> >[/color]
> I would use SET DB2INSTANCE=nam e 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 "DB2INSTANC E"
>
> If you need to change your environment variable *just* for current
> session, just issue:
>
> set DB2INSTANCE=new inst
>
> This is valid until end of this session (until you close the window).
>
>
> Jan M. Nelken[/color]

**Garfield Lewis** · Nov 12 '05, 06:01 AM

Re: db2batch from windows client: SQL1427N An instance attachment does not exist

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@profitl ogic.com> wrote in message
news:9573e6b2.0 312080950.3318a 7c8@posting.goo gle.com...[color=blue]
> 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\SQLLI B\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\SQLLI B\BIN>db2set -all
> [e] DB2PATH=C:\Prog ram Files\IBM\SQLLI B
> [g] DB2_DOCCDPATH=C :\Program Files\IBM\SQLLI B\
> [g] DB2SYSTEM=LIZAR D
> [g] DB2PATH=C:\Prog ram Files\IBM\SQLLI B,
> [g] DB2INSTDEF=LINU X0
> [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@I nvalid.Domain> wrote in message[/color]
news:<3fcf50c2_ 4@news1.prserv. net>...[color=blue][color=green]
> > Eugene wrote:[color=darkred]
> > > That is strange, but db2set fails to set DB2INSTANCE:
> > >
> > > C:\Program Files\IBM\SQLLI B\BIN>db2set DB2INSTANCE=lin ux0
> > >[/color]
> > I would use SET DB2INSTANCE=nam e 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 "DB2INSTANC E"
> >
> > If you need to change your environment variable *just* for current
> > session, just issue:
> >
> > set DB2INSTANCE=new inst
> >
> > This is valid until end of this session (until you close the window).
> >
> >
> > Jan M. Nelken[/color][/color]

**Gert van der Kooij** · Nov 12 '05, 06:01 AM

Re: db2batch from windows client: SQL1427N An instance attachment does not exist

In article <9573e6b2.03120 30716.1c86a084@ posting.google. com>,
eugene@profitlo gic.com says...[color=blue]
> 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
>[/color]

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 ?

**Eugene** · Nov 12 '05, 06:01 AM

Re: db2batch from windows client: SQL1427N An instance attachment does not exist

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.n l> wrote in message news:<MPG.1a3ee 8bf276095a9896b 1@news.xs4all.n l>...[color=blue]
> In article <9573e6b2.03120 30716.1c86a084@ posting.google. com>,
> eugene@profitlo gic.com says...[color=green]
> > 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
> >[/color]
>
>
> 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 ?[/color]

**Gert van der Kooij** · Nov 12 '05, 06:01 AM

Re: db2batch from windows client: SQL1427N An instance attachment does not exist

In article <9573e6b2.03120 81429.39535f7c@ posting.google. com>,
eugene@profitlo gic.com says...[color=blue]
> 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
>[/color]

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

**Eugene** · Nov 12 '05, 06:01 AM

Re: db2batch from windows client: SQL1427N An instance attachment does not exist

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$ag h$1@hanover.tor olab.ibm.com>.. .[color=blue]
> 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@profitl ogic.com> wrote in message
> news:9573e6b2.0 312080950.3318a 7c8@posting.goo gle.com...[color=green]
> > 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\SQLLI B\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\SQLLI B\BIN>db2set -all
> > [e] DB2PATH=C:\Prog ram Files\IBM\SQLLI B
> > [g] DB2_DOCCDPATH=C :\Program Files\IBM\SQLLI B\
> > [g] DB2SYSTEM=LIZAR D
> > [g] DB2PATH=C:\Prog ram Files\IBM\SQLLI B,
> > [g] DB2INSTDEF=LINU X0
> > [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@I nvalid.Domain> wrote in message[/color]
> news:<3fcf50c2_ 4@news1.prserv. net>...[color=green][color=darkred]
> > > Eugene wrote:
> > > > That is strange, but db2set fails to set DB2INSTANCE:
> > > >
> > > > C:\Program Files\IBM\SQLLI B\BIN>db2set DB2INSTANCE=lin ux0
> > > >
> > > I would use SET DB2INSTANCE=nam e 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 "DB2INSTANC E"
> > >
> > > If you need to change your environment variable *just* for current
> > > session, just issue:
> > >
> > > set DB2INSTANCE=new inst
> > >
> > > This is valid until end of this session (until you close the window).
> > >
> > >
> > > Jan M. Nelken[/color][/color][/color]