Hello,

We have a DB2 UDB v8.1 FP5 database on AIX 5.3 server. Sometime we encounter users trying to connect using the wrong password which locks the user. We would like to track the IP address of the unsuccessful attempts made.

I wanted to know is there any way from the database logs that we can track the connection attempts with the IP of the client fro where the attempt is made?

Thanks.

some useful information must be there in db2diag.log

Try this one:

1. List applications
2. Find the Application Id
Applicaion ID 17216851.1444.000B00180031 (for Example)

IP address (group two characters together and the convert from hex to dec - you can use Calculator program - just turn on Scientific option from View menu)
17=23
21=33
68=104
51=81

So IP adress is 23.33.104.81

Port number in hexa
1444 = 5188

You can track that with the DB2 Audit Facility. Check the documentation on how to set that up, but be careful to only limit reporting to unsucessful access attempts.

Putting DB2audt is a good idea as checking for db2diag.log file. But in db2audt we need to change the file path as well as the sizes. where as all the user info got registered in db2diag with the diag level 0/1 as well. So file size will be limited. As well in linux and mainframes db2audt can give u trouble for file(PS & PDS) path and permissions.

The DB2 Audit facility will capture all the necessary information about an unsuccessful connection attempt, including user, client IP address, etc. It is easy to set it up to only capture unsuccessful attempts. As for the rest of your comments, I have not idea what you are talking about.

BTW, if the only DB2 certification exam you are able to pass is the "Database Associate" exam (which is basically about SQL and not DB2 database administration), then you really don't know much about DB2 database administration.

Thanks for all the inputs.

The db2audit was on and I managed to get the information against the VALIDATE failure events.
However, I am still not sure about how to get the IP address. Here is a sample entry:

timestamp=2008-07-02-23.30.04.878565;category=VALIDATE;audit event=CHECK_GROUP_MEMBERSHIP;
event correlator=2;event status=-1092;
database=DB2INST;userid=DB2USER;authid=DB2USER;exe cution id=db2admin;
origin node=0;coordinator node=1;
application id=GA44094F.P1C2.0D0B72153004;application name=myapp;
auth type=SERVER_ENCRYPT;

How do I get the IP address. I checked in the Admin Implementation Guide but still am clueless about this.

GA44094F is the IP address. Convert each pair of Hex numbers to IP address in decimal:

GA.44.09.4F is in hex and convert these to decimal.

For a DRDA connection, if the first letter is above F, convert it as follows:
G=0
H=1
I=2
J=3
etc

10.68.09.79

Dear Sir
I really acknowledge ur knowledge . From u like persons we are learning many things.

Just one request..... while reply to the juniors u can a bit polite... Just a request from a junior DBA......


With regards........................................... ..

Where was Marcus impolite? Marcus is giving very qualified answers. If you don't like the way we discuss in this group, then just don't read it and/or ask questions. Besides, there are quite a few posts in this group that ignore some basic rules of politeness, for example the ones listed here: Must Read before posting

He didn't like when I said that someone who only passes the DB2 Associate Exam (which is basically about SQL) is not a real DBA.

I know that there are many excellent DB2 DBA's who are not certified, however, my position is that even if one passes the DB2 Associate Exam it would be best to not even mention anything about DB2 Certification in one's signature line until they pass the DB2 DBA Exam.

Ah, certifications. Nice topic...

It is a matter of perception, I guess... I see it exactly like you, Marcus. Anyone can pass the DB2 Associate certificate with just:

• basic knowledge of SQL and relational database systems
• learning a little bit about DB2 products and positioning,
• carefully reading the test questions and thinking about them.

To bring it even more to the point: he who fails the DB2 Associate certificate shouldn't work with any relational database system henceforth.

In the past, I had a group of master students who took a 3 month (4 hours per week) database administration course. Nearly all of them got the DB2 Admin certification, and close to 50% of them even managed to pass the DB2 Advanced Administration certification. Granted, a bit of luck was involved for some (good/friendly questions, accidentally choosing the right answer), but nevertheless it puts DB2 Associate into perspective.

Actually, if one has a good knowledge of SQL, one usually can afford to miss all the questions about DB2 products and positioning on the DB2 Associate Exam and still get a passing score.

Actually Regarding Mr. A impolite is a betterment.... I like ur suggestion like not reading the topics.... I'll do that.....


Its always better to avoid high ego persons............