Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 25

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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Join & Write a Comment

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

661 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