473,701 Members | 2,976 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Pre-set default schema for new user


When I create a new user in DB2, can I set the default schema to an
existing schema? The reason I want to do this is that I want to later
connect to db2 as this user and access tables WITHOUT the schema name
prefix. In addition to that, I want to use ADO.NET to connect as this
user and query tables in another schema without the prefix. (I use IBM
Ole DB Data provider in the connection string). The access is
currently set up properly. In other words, I can do everything I want
as long as I supply the prefix. I hope a "pre-set" can solve all my
problems. I know that you can do a "set current schema = xxx" after
you connect. But that is only good for that connection. Also, it is
difficult for me to make a change in the db access code which is a
third party software.

Jan 11 '06 #1
7 21353
AFAIK, at least in DB2 for LUW, there is no way to preset the default
schema at connection time (there is no such an option in CONNECT
statement). The default schema for user A is always A right after you
get connection to your database. You can use aliases in order to avoid
qualifying non-default schema objects with a schema name.


Jan 11 '06 #2
gr*********@yah oo.com wrote:

When I create a new user in DB2, can I set the default schema to an
existing schema?
First, you don't create users in DB2. Users are created at the operating
system level or by the means mandated by your specific security plugin.
The reason I want to do this is that I want to later
connect to db2 as this user and access tables WITHOUT the schema name

You should remember that the schema name is an integral part of the
qualified table name. Personally, I consider it as good coding style to
always fully qualify the table name with the proper schema name. It just
helps to avoid any problems with determining the proper table.

But you can have a look at the CLI configuration. It allows you to set a
default schema that shall be used for a particular database. But that
default will only apply to CLI applications (and whichever interface builds
on that). I don't know if there are other mechanisms available for other
binding styles.

Knut Stolze
DB2 Information Integration Development
IBM Germany
Jan 11 '06 #3
In Oracle, there is such a thing such a a LOGON TRIGGER, that is, a
TRIGGER that fires when the user logs in. Is there an equivalent for
DB2? That would allow a SET SCHEMA statement to be run upon every
logion automatically.


Jan 11 '06 #4
How do you use aliases to avoid qualifying non-default schema object
with a schema name?

Jan 11 '06 #5
I am connecting as user TS, that BTW has IMPLICIT_SCHEMA authority
granted (by default) to my db sandbox OAKL0 and do the following:

db2 => connect to oakl0 user ts
Enter current password for ts:

Database Connection Information

Database server = DB2/LINUX 8.2.1
SQL authorization ID = TS
Local database alias = OAKL0
db2 => values current schema



1 record(s) selected.
db2 => create table x.t (i int) ----------------------> this creates
table and the schema X if the latter didn't exist
DB20000I The SQL command completed successfully.
db2 =>
db2 => list tables for schema ts

Table/View Schema Type Creation time
------------------------------- --------------- -----

0 record(s) selected.

db2 => list tables for schema x

Table/View Schema Type Creation time
------------------------------- --------------- -----

1 record(s) selected.
db2 => create alias t for x.t
DB20000I The SQL command completed successfully.
db2 =>
db2 => list tables for schema ts

Table/View Schema Type Creation time
------------------------------- --------------- -----

1 record(s) selected.
db2 => insert into t values(1)
DB20000I The SQL command completed successfully.
db2 => select * from t


1 record(s) selected.

db2 => values current schema



1 record(s) selected.

db2 =>

Jan 11 '06 #6
Brian Tkatch wrote:
In Oracle, there is such a thing such a a LOGON TRIGGER, that is, a
TRIGGER that fires when the user logs in. Is there an equivalent for
DB2? That would allow a SET SCHEMA statement to be run upon every
logion automatically.


No log-on triggers in DB2. cli.ini is the standard answer to setting a
default schema. In many apps building up the connection isn't under end
user conbtrol anyway. So it can be easily wrapped.

Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab
Jan 11 '06 #7
Thanks guys for the responses. I found a solution.

First of all, let me respond to the suggestions:
1. alias - Good workaround for the developer, not good for DBA. Shot
down by DBA.
2. including schema name as good coding practice - totally agree.
However, we are using a third party software that generates data
classes from the DB2 tables. Table names are hard-coded in these data
classes. Does anyone know a good code-generation software that allow
you to easily change the schema name?
3. cli.ini - Not sure how it works. Didn't see any mentioning of
user/pwd in this file. Does that mean the "current schema" setting
will be for anyone who logged on to DB2 database on this computer?
Also, what type of connection actually uses this file?

Now, my solution is to use the ibm.data.db2 which is the IBM's version
of DB2 .Net data provider. It allows you to set the currentschema in
the connection string. It is exactly what I wanted. The only thing is
that I have to modify the third-party source code (not the generated
data classes) so that it will use the ibm.data.db2 when it sees this
connection string. I was going to make this upgrade anyways.
(previoulsy we were using IBM's OleDB provider.) I read that the MS
Ole DB provider for DB2 takes a default schema parameter as well.
However, I don't really know how to setup the packages as required by
the connection string. The IBM's .Net provider connection string is
easy to set up and use.

Jan 16 '06 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: GriffithsJ | last post by:
Hi I have been given some text that needs to be displayed on a web page. The text is pre-formatted (includes things like lists etc) and displays okay if I wrap it using the <pre/> tag. However, the font used is rather "naff" and looks too different to the rest of my web page. I'm not sure how I can (or even whether I can) override the font used with the <pre/> tag. If not, is there another tag I can use to display pre-formatted...
by: Alan Illeman | last post by:
How do I set several different properties for PRE in a CSS stylesheet, rather than resorting to this: <BODY> <PRE STYLE="font-family:monospace; font-size:0.95em; width:40%; border:red 2px solid; color:red;
by: Michael Shell | last post by:
Greetings, Consider the XHTML document attached at the end of this post. When viewed under Firefox 1.0.5 on Linux, highlighting and pasting (into a text editor) the <pre> tag listing will preserve formatting (white space and line feeds). However, this is not true when doing the same with the <code> tag listing (it will all be pasted on one line with multiple successive spaces treated as a single space) despite the fact that...
by: Jarno Suni not | last post by:
It seems to be invalid in HTML 4.01, but valid in XHTML 1.0. Why is there the difference? Can that pose a problem when such a XHTML document is served as text/html?
by: Rocky Moore | last post by:
I have a web site called HintsAndTips.com. On this site people post tips using a very simply webform with a multi line TextBox for inputing the tip text. This text is encode to HTML so that no tags will remain making the page safe (I have to convert the linefeeds to <BR>s because the Server.EncodeHTML does not do that it seems). The problem is that users can use a special tag when editing the top to specify an area of the tip that will...
by: Eric Lindsay | last post by:
I can't figure how to best display little snippets of shell script using <pre>. I just got around to organising to bulk validate some of my web pages, and one of the problems occurs with Bash shell pieces like this: <pre><code> #!/bin/sh ftp -i -n ftp.server.com&lt; &lt;EOF user username password epsv4 cd /
by: Xah Lee | last post by:
The Concepts and Confusions of Pre-fix, In-fix, Post-fix and Fully Functional Notations Xah Lee, 2006-03-15 Let me summarize: The LISP notation, is a functional notation, and is not a so-called pre-fix notation or algebraic notation. Algebraic notations have the concept of operators, meaning, symbols placed around arguments. In algebraic in-fix notation, different
by: Paul Connolly | last post by:
char *s = "Hello"; s = 'J'; puts(s); might print "Jello" in a pre-ANSI compiler - is the behaviour of this program undefined in any pre-ANSI compiler - or would it always have printed "Jello" with a pre-ANSI compiler? In gcc with the "writable-strings" option this program prints Jello If there were more than one semantics for what this progran did under a
by: Vadim Guchenko | last post by:
Hello. I'm using the following code: <html> <head> <style type="text/css"> pre {display: inline;} </style> </head>
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.