Equivalence for Sybase Functions in DB2

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pankaj_wolfhunter@yahoo.co.in - 02 Jan 2006 12:57 GMT

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

Reply to this Message

Serge Rielau - 02 Jan 2006 13:30 GMT

> Greetings,
> In Sybase there are some functions for which i am not
[quoted text clipped - 57 lines]
>
> 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

Signature

Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

Reply to this Message

Knut Stolze - 02 Jan 2006 14:01 GMT

> Greetings,
> In Sybase there are some functions for which i am not
[quoted text clipped - 11 lines]
>
> pubs2

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

VALUES CURRENT SERVER

> 2)
>
[quoted text clipped - 6 lines]
> ------
> 4

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

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

pankaj_wolfhunter@yahoo.co.in - 02 Jan 2006 14:57 GMT

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.

Reply to this Message

Knut Stolze - 02 Jan 2006 15:22 GMT

> 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

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

pankaj_wolfhunter@yahoo.co.in - 02 Jan 2006 15:29 GMT

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

Reply to this Message

pankaj_wolfhunter@yahoo.co.in - 02 Jan 2006 15:34 GMT

I executed db2level to check the fixpack

version: DB2 v8.1.7.445
FP 7

Does this help?

Reply to this Message

Serge Rielau - 02 Jan 2006 23:49 GMT

> 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

Signature

Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

Reply to this Message

pankaj_wolfhunter@yahoo.co.in - 03 Jan 2006 12:40 GMT

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

Reply to this Message

Knut Stolze - 03 Jan 2006 14:37 GMT

> Thanks for the reply Serge. yes, we are trying to overcome the problem
> that we are facing when migrating
[quoted text clipped - 3 lines]
> 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.

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

pankaj_wolfhunter@yahoo.co.in - 03 Jan 2006 14:45 GMT

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.

Reply to this Message

Knut Stolze - 03 Jan 2006 16:11 GMT

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

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message