Results 1 to 15 of 15
  1. #1
    Join Date
    Sep 2003
    Posts
    78

    Unanswered: db2 backup error

    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

    $

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Please post the output of "list node directory" and "list db directory". What login ID do you use to run the backup script?
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Sep 2003
    Posts
    78
    I'm running cron under the install id. Works on the one, but not on the second.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What's the "backup.sh" script? Does it source sqllib/db2profile?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Sep 2003
    Posts
    78
    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.

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Sep 2003
    Posts
    78
    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.

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by n_i
    Please post the output of "list node directory" and "list db directory".
    ..........................
    ---
    "It does not work" is not a valid problem statement.

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Sep 2003
    Posts
    78
    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.

  11. #11
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  12. #12
    Join Date
    Sep 2003
    Posts
    78
    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 =

  13. #13
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Exactly as Herr Stolze predicted: CMSHAWK is cataloged with the AUTHENTICATION SERVER option - this is why it requires you to authenticate with the password.
    ---
    "It does not work" is not a valid problem statement.

  14. #14
    Join Date
    Sep 2003
    Posts
    78
    That was it...Thanks a lot!!!

  15. #15
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by n_i
    Exactly as Herr Stolze predicted: CMSHAWK is cataloged with the AUTHENTICATION SERVER option - this is why it requires you to authenticate with the password.
    Thanks for figuring this one out for me.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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