Considering DB2, but have questions?

[sobeit]

The questions that I have for the DB2 USB (Windows) are:
1)Security - can i use Windows Authentication but still limit certain group of users (i.e., developers) to certain databases?

2) Backup - can a complete backup be done on a database while users are using it?
3)Can the data files (database files) be backup using ARCHSERV OR other backup tool, while the database is up and running?
Currently we have users, who users whome use MS Access and Crystal Report application to link to SQL Server tables and run querries against these linked tables. Can the same users re-linked the DB2 table versions and use Access and Crystal Reports to pull data?


Thanks.

 

[blom0344]

Hello Sobeit,

I run a datawarehouse on DB2 with users using ODBC to run reports and queries on it, so I should be able to provide you with some answers.

Security.

DB2 loans all identification from the operating system, so if you try to access with an account it is per se a operating account. Easiest thing to do it to create different schema's to set up security. Create a schema and DB2 will provide a user with the same name.


Backup.

Now this is quite a complex story. DB2 has 2 types of logging: archive and circular logging. With the latter log files are overwritten after a while , so you cannot do a rollback of the database. Also with this type of logging you can only do a offline backup, DB2 allows no connections from users busy with Access. If you plan a backup (schedule) late in the evening this is however no problem.
With archive logging, logfiles are just expanding, you can do an online backup with connections in the air. Of course this requires tending to your log-files !!

Archserv

We do a backup of the filesystem every night without shutting down the instance or databases. Backup of the system does not fail if a connection to the server is open.

Reporting

Do NOT expect to have SQL server type SQL to work directly without problems on DB2 , cause SQL syntax is not the same. However, when you use Access, it uses its own JET-SQL type, which somehow is translated to the native SQL of the underlying database. Be aware that this is a compromise anyway. If you use a reporttool that uses the DB2 dialect of the database performance can be very much improved !!


ODBC

DB2 7.1 / 7.2 is very finicky about ODBC set-up. If you use Access you have to tune the connection to make it work. If you do not do this at ODBC level, connecting manually to the database will give connection problems. If you want more info about this, use the direct mail way to my office.

T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com