Connection Error

[chulett]

Been through the forum but can't seem to find anything that fixes the problem that has suddenly cropped up today. And before anyone asks, no I have no idea what may have changed, as there is nothing pertinent to this that either I am aware of or should have changed.

Some users can connect just fine, including our generic 'dsuser' and the ever popular 'dsadm'. Most indivual users can connect as well, however some cannot - myself included. I don't see anything different about the ones that can versus the ones that can't except that I know there must be. All have their primary group set to 'dstage'. The error presents itself in two dialogues when we attempt to connect.

First the dreaded subroutine error:

Error calling subroutine: *DataStage*DSR.ADMIN (Action=11); check DataStage is set up correctly in project XXXX
(Subroutine failed to complete successfully (30107))

Clicking OK gets me:

ACCESS DENIED: Internal SQL error.
Failed to register this user for SQL.

And then I'm done. Looked thru many similar posts, most of which don't ever seem to come to a conclusion. I've check permissions under DSEngine, including in the DS_LICENSE hashed file and in the projects as well. I've seen Kim's posts about doing 'chmod -R 777' on various bits and they all seem ok.

What can I look for? Try? Sacrifice?

[kcbland]

It's either PC or server related. If you can't connect on your PC, can you connect on someone else's?

If not, then the problem is server side. Are all projects out of bounds? How about a different server? Can you connect to dev but not prod? Are you using a "roaming" home?

It seems like a permissions style issue. Any chance permissions to /tmp or your home directories are different?

[chulett]

Definitely server related as the client doesn't matter. And yes, all projects are 'out of bounds' as none on this particular server can be connected to. My production server is still fine, so whatever changed changed just on the dev/test server. No, no roaming home.

I agree, it does seem like a permissions issue but I can't seem to find anything yet that looks like a problem, /tmp or otherwise. What objects are involved with the 'registration' of a user for SQL? Maybe a grant needs to restored? Grasping at straws here...

[ray.wurlod]

Get in via telnet, and find out who the DBA is.

Code: Select all

SELECT * FROM UV_USERS WHERE DBAAUTH = 'YES';

Establish a telnet session as that user, and grant connect privilege to those users.

Code: Select all

GRANT CONNECT TO user;

If the original DBA user no longer exists, you may need to uninstall and re-install the DataStage server, in order to set up a new DBA user.

[chulett]

You had an extra 'A' - it's DBAUTH. And here's what happens when I run that:

Code: Select all

>SELECT * FROM UV_USERS WHERE DBAUTH = 'YES';


User Name.......... dsadm
DBauth Privilege... YES
Resource Privilege. YES
Author............. dsadm
Own-Table Schema.. Owned Tables......
CATALOG            UV_TABLES
CATALOG            UV_COLUMNS
CATALOG            UV_SCHEMA
CATALOG            UV_VIEWS
CATALOG            UV_ASSOC
CATALOG            UV_USERS
CDC-Dev            EXAMPLE1
CDC-Test           EXAMPLE1
DPC-Dev            EXAMPLE1
DPC-Pet            EXAMPLE1
DPC-Test           EXAMPLE1
EIW-Dev            EXAMPLE1
EIW-Test           EXAMPLE1
FinanceDev         EXAMPLE1
FinanceTest        EXAMPLE1
HR-Dev             EXAMPLE1
HR-Test            EXAMPLE1
VERSION            EXAMPLE1
VERSION-CDC        EXAMPLE1
VERSION-DPC        EXAMPLE1
VERSION-EIW        EXAMPLE1
VERSION-HR         EXAMPLE1
VERSION-sandbox    EXAMPLE1
sandbox            EXAMPLE1
Perm-Table Schema. Permitted Tables..


Read operation failure.  Internal file corruption detected.  File must be repaired.

Now what? And in case you are wondering, it was not a space issue that corrupted it, that is tightly monitored and is sitting at a 51% usage level right now. Hmmm... unless it went up and back down again between monitor pings...

[ray.wurlod]

Run fixtool or uvfixfile in verify mode (that is, without the -fix option) against UV_USERS to find out what's wrong and whether it might be repairable.

[ray.wurlod]

If it's repairable, you might like to attempt that. You will need to be logged in with Administrator privilege.

Otherwise, restore UV_USERS from backup with DataStage shut down and after renaming the bad one, and hope like crazy that the backup is not also corrupted (you will find out soon enough).

I'm going to go out on a limb and forecast that it's a backward link (BLINK) error that will be reported. These can usually be repaired by sufficiently skilled personnel; there are some in the IBM/Ascential support center. Basically they open the hashed file with a hex editor and fix the damaged pointer(s): not a job for the faint-hearted.

However if UV_USERS has been truncated by a friendly disk utility, such as fsck, then you will have no choice but to restore from backup.

[chulett]

You'd have to tell me if this means it is repairable or not... I'm assuming it is but will wait until our scheduled maintenance window tonight (or a reply back) to find out.

Code: Select all

>UVFIXFILE UV_USERS
File modulus is greater than physical file.
Invalid nextsplit ptr in header.

Beginning TRACE of sql/catalog/UV_USERS.
ID "root" doesn't hash to primary group 1.
ID "chulett" doesn't hash to primary group 1.
ID "kpurcel" doesn't hash to primary group 1.
ID "bwillia" doesn't hash to primary group 2.
ID "cnothwe" doesn't hash to primary group 2.
ID "jrucins" doesn't hash to primary group 2.
TRACE of sql/catalog/UV_USERS completed.

Warning: file load discrepancy detected.

Scanning overflow buffers.
Scan complete.

2 group(s) processed.
2 group buffer(s) processed.
37 record(s) processed.
Number of data bytes = 1928.

What would be the best way to check everything out, in essence a Level 3 Diagnostics? Seems like more than a 'reindexing' of all projects would be in order...

[ray.wurlod]

File modulus is greater than physical file may mean that the file has been truncated by a disk utility or that the current modulus figure has not been successfully flushed back into the file header when the file was closed.

If it has been truncated, there's nought you can do save restore from backup. If only the header information is wrong, it may be repairable (probably is, but don't go there just yet).

Take a backup of the UV_USERS directory (which contains DATA.30, OVER.30 and .Type30 files). An operating system level recursive copy (cp -ipr) will suffice.

Please provide the following information.
1. What are the physical sizes of DATA.30 and OVER.30?
2. Can you SELECT COUNT(*) FROM UV_USERS; successfully?
3. What is the result of ANALYZE.FILE UV_USERS STATS command?

Reindexing won't help at all - note that UV_USERS is not in any project. This is a damaged hashed file that is part of the DataStage/SQL "system" tables.

If you want to attempt a repair, use the FIX option of UVFIXFILE or the -fix option of fixtool or uvfixfile. Don't do this unless you have a backup copy; even these utilities have been known to "repair" hashed files by truncating them.

Otherwise wait, and maybe we can be a little more specific about what additional diagnoses you can do, stepping through the file with uvfixfile in interactive mode or using higher verbosity levels with the tool. But before attempting that, I would require the information I asked for above.

[chulett]

Thanks.

1) Both DATA.30 and OVER.30 are 4096 bytes with dates from 2005.
2) No, I get the same 'read operation failure' as noted before.
3) First it lists the filename, then 'File has 4 groups' and then lastly a similar 'read operation failed' and 'Internal data error'.

Doesn't seem like a repair will actually be able to repair anything, eh? Could I transfer this from one of the other DS servers? I have no idea how long it would take to request and get this restored from backup.

FYI... UV_USERS from a secondary server is fine (of course) but only has three users in it: root, dsuser and dsadm. Will any others be added automatically as they connect?

[chulett]

Interesting... in spite of the fact that 'dsadm' is the administration user and has 'DBAUTH' privledges, I can't "FIX" UV_USERS. First thing it says when I try is:

User does not have superuser privledges!
Disabling the FIX option and continuing.

What, I have to be root? That's not gonna happen...

[ray.wurlod]

You (or someone) does have to be superuser to use the FIX option on UVFIXFILE. You may have better success with fixtool (which, being more recent, may recognize dsadm). But no guarantees.

DBA privilege is unrelated to these tools. You need to have O/S admin access.


That DATA.30 is 4096 bytes long implies that the current modulus of UV_USERS should be 1 (one x 2KB header and one x 2KB group), but the "current groups" figure of 4 indicates that the recorded current modulus is 4. Either of my earlier diagnoses remains possible. Has any disk utility (such as fsck) been run in the recent past?

Since UV_USERS is not indexed, it may be safe to move one from another server. The problem with that is, if any user has created a DataStage/SQL object (such as a UV table), the ownership of that will be broken, and may have to be repaired with VERIFY.SQL once the user has been re-established.

Will this happen automatically? Theoretically yes, but if it isn't you can always grant CONNECT privilege as discussed earlier.

[chulett]

I'll give fixtool a shot and let you know what happens. And what comes of any transplants of the hashed file from another server.

[ray.wurlod]

At some sites where they won't give you root access (understandably) they are happy that you instruct the person who does have root access through the required steps. At least that's been my experience.

Good luck.

[chulett]

Yah, same here. However, since this is 'only a development server' I won't be able to get my virtual hands on one until tomorrow during 'working hours'.

Thanks.