I'm at V8 FP14 DB2 on AIX. I have an instance that's been created for several years. It contains a database created using db2inst1 which is the install id and not the instance owner id. It backs up fine from the GUI and from CRON. We created another database in this instance. It was created using the instance owner ID. It backs up fine from the GUI, but gives me a backup error in CRON. It says that the password/ID is missing. If I alter my backup script to provide userid and password it works. DB2inst1 has all user AIX and DB2 rights on both databases as does the owning instance. I'm not getting it...HELP PLEASE???


$ cat backup
CONNECT TO cmshawk;
QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;
CONNECT RESET;
BACKUP DATABASE cmshawk TO "/cmsbackt" WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTING;
CONNECT TO cmshawk;
UNQUIESCE DATABASE;
CONNECT RESET;
$ backup.sh

$ cat backup.rpt
SQL30082N Attempt to establish connection failed with security reason "3"
("PASSWORD MISSING"). SQLSTATE=08001

SQL1024N A database connection does not exist. SQLSTATE=08003

SQL1024N A database connection does not exist. SQLSTATE=08003

SQL30082N Attempt to establish connection failed with security reason "3"
("PASSWORD MISSING").

SQL30082N Attempt to establish connection failed with security reason "3"
("PASSWORD MISSING"). SQLSTATE=08001

SQL1024N A database connection does not exist. SQLSTATE=08003

SQL1024N A database connection does not exist. SQLSTATE=08003

$

Please post the output of "list node directory" and "list db directory". What login ID do you use to run the backup script?

I'm running cron under the install id. Works on the one, but not on the second.

What's the "backup.sh" script? Does it source sqllib/db2profile?

Yes, backup.sh runs the db2profile and then runs "backup". there are 2 databases in the same instance. db2inst1 and instanceowner have full control over both databases. dbs1 created under db2inst1 and dbs2 created under the instanceowner id. dbs1 backs up with cron and gui. dbs2 backs up with gui but doesn't work with the same cron job.

I don't get this "db2inst1" vs. "instanceowner" thing. Which user owns the DB2 instance? It doesn't seem to be "db2inst1". If so, which privileges and authorizations does "db2inst1" have in your instance?

Also, could you show us the backup.sh skript, please?

cmsdev owns the instance. db2inst1 is dbadm. if you look at the privilages on both databases and compare both id's, they are the same.

Cron JOBS are set up to run under DB2INST1 ID

$ cat backup.sh
echo
. /home/db2inst1/sqllib/db2profile
db2 -otf /db2scripts/backup > /db2scripts/backup.rpt

$ cat backup
CONNECT TO cmshawk;
QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;
CONNECT RESET;
BACKUP DATABASE cmshawk TO "/cmsbackt" WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTING;
CONNECT TO cmshawk;
UNQUIESCE DATABASE;
CONNECT RESET;

$ cat backup.rpt
SQL30082N Attempt to establish connection failed with security reason "3"
("PASSWORD MISSING"). SQLSTATE=08001

SQL1024N A database connection does not exist. SQLSTATE=08003

SQL1024N A database connection does not exist. SQLSTATE=08003

SQL30082N Attempt to establish connection failed with security reason "3"
("PASSWORD MISSING").

SQL30082N Attempt to establish connection failed with security reason "3"
("PASSWORD MISSING"). SQLSTATE=08001

SQL1024N A database connection does not exist. SQLSTATE=08003

SQL1024N A database connection does not exist. SQLSTATE=08003

NOTHING SHOWS UP IN DB2DIAG.LOG!!!
If I change the database name from CMSHAWK to COMMCMSD, it works correctly.

..........................

I would say you have to fix this message: SQL30082N Attempt to establish connection failed with security reason "3"

Can you run the backup.sh script as user "db2inst1" standalone, i.e. not through cron?

Yes I can. I created a test script to just do a simple connect.

Both databases are in the same instance and writing to the same "data" directory. I created a test script to do a simple "connect" statement. The CMSHAWK gave me a SQL30082N and the COMMCMSD connnected fine.

I'm running the shell command tst.sh from the command line...outside of CRON. I get the same results either way.

$ tst.sh

$ cat tst.sh
echo
. /home/db2inst1/sqllib/db2profile
db2 -otf /db2scripts/tst > /db2scripts/tst.rpt



$ cat tst
CONNECT TO cmshawk;
CONNECT RESET;

connect to commcmsd;
connect reset;



$ cat tst.rpt
SQL30082N Attempt to establish connection failed with security reason "3"
("PASSWORD MISSING"). SQLSTATE=08001

SQL1024N A database connection does not exist. SQLSTATE=08003


Database Connection Information

Database server = DB2/6000 8.2.7
SQL authorization ID = DB2INST1
Local database alias = COMMCMSD


DB20000I The SQL command completed successfully.

$


Now, if I put the password in, it works fine.

$ tst.sh

$ cat tst.sh
echo
. /home/db2inst1/sqllib/db2profile
db2 -otf /db2scripts/tst > /db2scripts/tst.rpt

$ cat tst
CONNECT TO cmshawk user db2inst1 using ******;
CONNECT RESET;

connect to commcmsd;
connect reset;


$ cat tst.rpt

Database Connection Information

Database server = DB2/6000 8.2.7
SQL authorization ID = DB2INST1
Local database alias = CMSHAWK


DB20000I The SQL command completed successfully.


Database Connection Information

Database server = DB2/6000 8.2.7
SQL authorization ID = DB2INST1
Local database alias = COMMCMSD


DB20000I The SQL command completed successfully.

My guess: both databases reside in different instances and those instances are differently configured. E.g. one is configured with authentication SERVER and the other CLIENT. As n_i already asked you twice, show us the output of the "list node directory" and "list db directory" commands.

no...both databases reside in the same instance, as you can see


$ db2 list node directory

Node Directory

Number of entries in the directory = 10

Node 1 entry:

Node name = CFSDEV
Comment =
Directory entry type = LOCAL
Protocol = LOCAL
Instance name = cfsdev

Node 2 entry:

Node name = CMSDEV
Comment =
Directory entry type = LOCAL
Protocol = LOCAL
Instance name = cmsdev

Node 3 entry:

Node name = DB2ANODE
Comment =
Directory entry type = LOCAL
Protocol = TCPIP
Hostname = cmcf4.****************************
Service name = 2110

Node 4 entry:

Node name = DB2HNODE
Comment =
Directory entry type = LOCAL
Protocol = TCPIP
Hostname = cmcf1.****************************
Service name = 2110

Node 5 entry:

Node name = DBTANODE
Comment =
Directory entry type = LOCAL
Protocol = TCPIP
Hostname = cmcf4.****************************
Service name = 2111

Node 6 entry:

Node name = DBTHNODE
Comment =
Directory entry type = LOCAL
Protocol = TCPIP
Hostname = cmcf1.****************************
Service name = 2111

Node 7 entry:

Node name = DHSDEV
Comment =
Directory entry type = LOCAL
Protocol = LOCAL
Instance name = dhsdev

Node 8 entry:

Node name = DPHDEV
Comment =
Directory entry type = LOCAL
Protocol = LOCAL
Instance name = dphdev

Node 9 entry:

Node name = NDE45900
Comment =
Directory entry type = LOCAL
Protocol = TCPIP
Hostname = cmcf3.****************************
Service name = 2111

Node 10 entry:

Node name = NDEEC04A
Comment =
Directory entry type = LOCAL
Protocol = TCPIP
Hostname = ****************************.220.2
Service name = 2114

$
System Database Directory

Number of entries in the directory = 14

Database 1 entry:

Database alias = DB2TGLOC
Database name = DCS6AEE1
Node name = NDE45900
Database release level = a.00
Comment =
Directory entry type = Remote
Authentication = SERVER
Catalog database partition number = -1
Alternate server hostname =
Alternate server port number =

Database 2 entry:

Database alias = DB2THLOC
Database name = DB2TH
Node name = DBTHNODE
Database release level = a.00
Comment =
Directory entry type = Remote
Authentication = SERVER
Catalog database partition number = -1
Alternate server hostname =
Alternate server port number =

Database 3 entry:

Database alias = DB2TLOC
Database name = DCS2DDA9
Node name = NDEEC04A
Database release level = a.00
Comment =
Directory entry type = Remote
Authentication = SERVER
Catalog database partition number = -1
Alternate server hostname =
Alternate server port number =
Database 4 entry:

Database alias = TOOLSDB
Database name = TOOLSDB
Local database directory = /home/db2inst1
Database release level = a.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =

Database 5 entry:

Database alias = COMMCFSD
Database name = COMMCFSD
Node name = CFSDEV
Database release level = a.00
Comment =
Directory entry type = Remote
Authentication = SERVER
Catalog database partition number = -1
Alternate server hostname =
Alternate server port number =

Database 6 entry:

Database alias = COMMDPHD
Database name = COMMDPHD
Node name = DPHDEV
Database release level = a.00
Comment =
Directory entry type = Remote
Authentication = SERVER
Catalog database partition number = -1
Alternate server hostname =
Alternate server port number =

Database 7 entry:

Database alias = DB2ALOC
Database name = DB2A
Node name = DB2ANODE
Database release level = a.00
Comment =
Directory entry type = Remote
Authentication = SERVER
Catalog database partition number = -1
Alternate server hostname =
Alternate server port number =

Database 8 entry:

Database alias = COMMDHSD
Database name = COMMDHSD
Node name = DHSDEV
Database release level = a.00
Comment =
Directory entry type = Remote
Authentication = SERVER
Catalog database partition number = -1
Alternate server hostname =
Alternate server port number =

Database 9 entry:

Database alias = COMMCMSD
Database name = COMMCMSD
Node name = CMSDEV
Database release level = a.00
Comment =
Directory entry type = Remote
Catalog database partition number = -1
Alternate server hostname =
Alternate server port number =

Database 10 entry:

Database alias = DB2HLOC
Database name = DB2H
Node name = DB2HNODE
Database release level = a.00
Comment =
Directory entry type = Remote
Authentication = SERVER
Catalog database partition number = -1
Alternate server hostname =
Alternate server port number =

Database 11 entry:

Database alias = DB2TALOC
Database name = DB2TA
Node name = DBTANODE
Database release level = a.00
Comment =
Directory entry type = Remote
Authentication = SERVER
Catalog database partition number = -1
Alternate server hostname =
Alternate server port number =

Database 12 entry:

Database alias = SAMPLE
Database name = SAMPLE
Local database directory = /home/db2inst1
Database release level = a.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =

Database 13 entry:

Database alias = CMSHAWK
Database name = CMSHAWK
Node name = CMSDEV
Database release level = a.00
Comment =
Directory entry type = Remote
Authentication = SERVER
Catalog database partition number = -1
Alternate server hostname =
Alternate server port number =

Database 14 entry:

Database alias = ELRT01
Database name = ELRT01
Node name = DPHDEV
Database release level = a.00
Comment =
Directory entry type = Remote
Authentication = SERVER
Catalog database partition number = -1
Alternate server hostname =
Alternate server port number =

Exactly as Herr Stolze predicted: CMSHAWK is cataloged with the AUTHENTICATION SERVER option - this is why it requires you to authenticate with the password.

That was it...Thanks a lot!!!

Thanks for figuring this one out for me.