I try to setup federation in db2 v.9 for z/OS.
I have inserted necessary values into system tables:
Remote database which I want to connect to from mvs side is in DB2 v.10 on zLinux.
Now I try to select data from remote tables using db2 admin tool and there I have two cases:
1) In SYSIBM.USERNAMES I inserted db2 instance owner id as remote user id. This user id has all privileges in linux database.
When I execute select statement:
select * from location.remoteschema.remotetable
I don't have any issue, I get values.
But instance id can't be used as we want to provide access just to some tables on linux side. So we have to use another user id which has access only to required tables.
Then I have the second case:
2) In SYSIBM.USERNAMES I inserted remote user id which has connect privilege on linux database and select privilege on particular table.
But when I try to execute the above select statement I get following error:
SQLCODE = -551, SQLSTATE = 42501, SYNTAX ERROR OR ACCESS RULE
VIOLATION FROM DB2 UDB for AIX, Linux, HP-UX, Sun, and Windows TOKENS USERID EXECUTE ADBL.ADBMAIN
I assume that remote user id should have some more privileges in linux database but I have no idea what exactly missed.
And I can't find what does mean EXECUTE ADBL.ADBMAIN.
Could anybody help me found why I can access remote tables with instance id and can't with another user, please.