470,594 Members | 1,444 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,594 developers. It's quick & easy.

Equivalence for Sybase Functions in DB2

Greetings,
In Sybase there are some functions for which i am not
able to find any equivalent in DB2.

1)

DESCRIPTION: To obtain the database name one is currently logged into.

QUERY
1> select db_name()
2> go

RESULT

pubs2

(1 row affected)

2)

DESCRIPTION: To obtain the database id one is currently logged into
QUERY
1> select db_id()
2> go
RESULT
------
4

(1 row affected)

3)

DESCRIPTION: To retrieve the host id on which the database server is
running
QUERY
1> select host_id()
2> go
RESULT
---------------------
8746

(1 row affected)

4)

DESCRIPTION: Retrieves the name of the database server
QUERY
1> select host_name()
2> go

RESULT
-----------------------
ps-server1

(1 row affected)
Any help would be greatly appreciated

TIA

Jan 2 '06 #1
11 1681
pa***************@yahoo.co.in wrote:
Greetings,
In Sybase there are some functions for which i am not
able to find any equivalent in DB2.

1)

DESCRIPTION: To obtain the database name one is currently logged into.

QUERY
1> select db_name()
2> go

RESULT

pubs2

(1 row affected)

2)

DESCRIPTION: To obtain the database id one is currently logged into
QUERY
1> select db_id()
2> go
RESULT
------
4

(1 row affected)

3)

DESCRIPTION: To retrieve the host id on which the database server is
running
QUERY
1> select host_id()
2> go
RESULT
---------------------
8746

(1 row affected)

4)

DESCRIPTION: Retrieves the name of the database server
QUERY
1> select host_name()
2> go

RESULT
-----------------------
ps-server1

(1 row affected)
Any help would be greatly appreciated

TIA

Which version/fixpack ar eyou on.
There are some table functions like GET_ENV_INFO() which return some of
the stuff.
The DB name is returned with eteh SQLCA when you connect.
There are other interesting fucntions like the SNAPSHOT_* function which
may dump the dbnme. If all fails I think teh DBINFO structure should
contain the dbname (a couple of lines of a Java or C function will make
it accessible through SQL.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab
Jan 2 '06 #2
pa***************@yahoo.co.in wrote:
Greetings,
In Sybase there are some functions for which i am not
able to find any equivalent in DB2.

1)

DESCRIPTION: To obtain the database name one is currently logged into.

QUERY
1> select db_name()
2> go

RESULT

pubs2

The name of the database can be found in the CURRENT SERVER special
register:

VALUES CURRENT SERVER
2)

DESCRIPTION: To obtain the database id one is currently logged into
QUERY
1> select db_id()
2> go
RESULT
------
4


DB2 doesn't have such an id for database. The name is the identifier.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Jan 2 '06 #3
Serge: I dont know about the fixpack but i am using db2 v8.2 on windows
platform.
About the functions u just specified, where can i find some working
examples on them?
Can u help with that.

Knut: thanks for the info.

Jan 2 '06 #4
pa***************@yahoo.co.in wrote:
Serge: I dont know about the fixpack but i am using db2 v8.2 on windows
platform.
About the functions u just specified, where can i find some working
examples on them?


In the SQL Reference: http://tinyurl.com/be424

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Jan 2 '06 #5
thanks for the reply again.

Well i executed the query again and this time with

db2updv8 -d databasename

but i am still getting the same error.

I checked the application objects section for this in control center
and it does not show the
ENV_GET_SYS_INFO() function.

What should i do to make this thing work?

TIA

Jan 2 '06 #6
I executed db2level to check the fixpack

version: DB2 v8.1.7.445
FP 7

Does this help?

Jan 2 '06 #7
pa***************@yahoo.co.in wrote:
I executed db2level to check the fixpack

version: DB2 v8.1.7.445
FP 7

Does this help?

The *ENV* functions were introduced in FP9 (you find them documented
online).
You are doing a Sybase migration right now? Why are you on FP7?
Assuming you use SQL Procedures I would recommend FP9 or higher because
SQL Procedures got completely revamped in V8.2 (FP7).
If you use triggers in the original Sybase design I recommend you also
pick up FP11 as soon as it becomes available. It will contain the
lifting of the "mutating table comflict" errors which are common when
doing SQL Server/Sybase migrations.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab
Jan 2 '06 #8
Thanks for the reply Serge. yes, we are trying to overcome the problem
that we are facing when migrating
from Sybase to DB2.

I dont know about the FP cause this is what these people are using. I
would surely recommend to go
go for higher FP.

I am still not able to find anything for retrieving hostid. i mean like
*ENV* do we have something to retreive the hostid also.
Also, is there any concept of server user id(suserid) in DB2? If yes,
would like to know how to get this?

TIA

Jan 3 '06 #9
pa***************@yahoo.co.in wrote:
Thanks for the reply Serge. yes, we are trying to overcome the problem
that we are facing when migrating
from Sybase to DB2.

I dont know about the FP cause this is what these people are using. I
would surely recommend to go
go for higher FP.
If you look at the information center, you will see a small "9" on the left
side for the function description. That indicates that something changed
in the description in the respective FixPak, i.e. FP9. Given the amount of
changes, one could deduct that the logic is completely new in FP9.
I am still not able to find anything for retrieving hostid.
What is the host-id supposed to be? DB2 doesn't have the concept of
host-ids as a database server is identified by its name or IP address (in
conjunction with the port number).
Also, is there any concept of server user id(suserid) in DB2? If yes,
would like to know how to get this?


Again, what is a user-id supposed to be? If you don't use specific user
exits (security plugins) for authorization, DB2 falls back to authenticate
users based on the operating system. But if you have a security plugin,
the plugin could do whatever it likes to authenticate users and then there
might not even be the uids that you have in the OS. The same
considerations apply to the handling of groups (aka roles).

In short, DB2 identifies users by their name.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Jan 3 '06 #10
Thanks a ton Knut. I know i am gonna come up with more questions (may
be very silly), as this is my first time,
but i dont know where else to go. :-)
Thanks again all.

Jan 3 '06 #11
pa***************@yahoo.co.in wrote:
Thanks a ton Knut. I know i am gonna come up with more questions (may
be very silly), as this is my first time,
but i dont know where else to go. :-)


That's all right. This is the reason why the group exists in the first
place.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Jan 3 '06 #12

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by William Holroyd | last post: by
reply views Thread by Data Goob | last post: by
1 post views Thread by sleepite | last post: by
2 posts views Thread by eight02645999 | last post: by
1 post views Thread by db2sysc | last post: by
3 posts views Thread by Alex Murphy | last post: by
26 posts views Thread by codercode | last post: by
reply views Thread by =?ISO-8859-1?Q?S=E9bastien_Sabl=E9?= | last post: by
reply views Thread by =?ISO-8859-1?Q?S=E9bastien_Sabl=E9?= | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.