473,604 Members | 2,710 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Pre-set default schema for new user

Hi,

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.
Thanks,

Jan 11 '06 #1
7 21323
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.

-Eugene.

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

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
prefix.


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.

B.

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

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

------------------------------

TS
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
------------------------------- --------------- -----
--------------------------
T X T
2006-01-11-13.27.23.025428

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
------------------------------- --------------- -----
--------------------------
T TS A
2006-01-11-13.28.14.313375

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

I
-----------
1

1 record(s) selected.

db2 => values current schema

1

----------------------------

TS
1 record(s) selected.

db2 =>
-Eugene

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.

B.

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.

Cheers
Serge
--
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

2
3094
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...
7
18524
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;
5
718
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...
8
3776
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?
7
2737
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...
9
5538
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 /
23
3611
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
7
4847
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
12
5566
by: Vadim Guchenko | last post by:
Hello. I'm using the following code: <html> <head> <style type="text/css"> pre {display: inline;} </style> </head>
0
7929
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8419
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8274
tracyyun
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...
0
6737
agi2029
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...
1
5882
isladogs
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...
0
3906
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
3954
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2433
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
1
1522
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.