Go Premium for a chance to win a PS4. Enter to Win


Federated database nick schemas

Posted on 2017-05-29
Medium Priority
Last Modified: 2017-09-14
We have prod environment and development environment  for DB2 .

1> we are thinking of proposing have a nick schema in dev pointing to prod .
in this case we will need to make sure only specific user id will have access to these nick schema .
is it possible to do that ?

2> if we fire select on  nick schemas< with ur > ; Is it sure that we will not lock  not lock the database tables .

3> Is there any way of controlling database load on the prod databases when queries are fired by nick schema ?
Question by:hemanth hebbur shekar
LVL 46

Expert Comment

by:Kent Olsen
ID: 42155333
Hi Hemanth,

I'm not familiar with the term "nick schema".  But a lot of your questions are generic in nature.

1)  DB2 defaults to the underlying O/S for authentication.  By default, all users defined to the O/S on the dev server will have some access to your new database.  You'll want to specifically exclude all users that you don't want to have access to the new database or install another authentication module, like Kerberos.

2)  Yes.  There have been some small variances in behavior of isolation level UR, but the general behavior is that the query does not place locks on data rows or pages.  The only exception that I know of is that there can be an S lock applied to the table if a delete is performed based on a query with UR, but that doesn't seem to be your case.

3)  I don't know of any.  You may, at a network level, limit the number of connections from DEV to PROD on the DB2 port, but once the query begins on PROD it's competing for resources like all the other queries.
LVL 26

Expert Comment

by:Tomas Helgi Johannsson
ID: 42211357

1) As Kent says then DB2 defaults to the underlying O/S for authentication,  however if you have done the security right by issuing correct granting in the db as well as created the db as restricted (that eliminates PUBLIC access) then by create-ing a separate schema for accessing remote objects can be done and granted to those specific users.

2) Kent explained this.

3) Using workload management you can control the resource usage for particular user/connection by creating a service class and workload object and set your limits.  In your case for the federation user.
See more here

      Tomas Helgi

Expert Comment

by:Julian Kiper
ID: 42295032
I think that the answer you are looking for is in the same CREATE SERVER sentence. When you create a server you will specify an user and password, so this info is what you should use for security and for monitoring reasons (I understood that for "load" you mean for monitoring queries execution from dev against prod).

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Join & Write a Comment

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question