Posts Tagged ‘DB2
Along with host language embedded SQL type applications, there are also embedded Java applications, better known as SQLJ programs. SQLJ is a method for accessing DB2 from a Java application that supports static execution. Again, the benefits of a static execution are reduced resource consumption, improved diagnostics, improved security, and greater repeatability of SQL performance due to static access paths and plans. Everything you need to get from the data is already in the package bound at bind time.
SQLJ provides performance benefits of static query execution by embedding SQL queries into Java applications. SQLJ still utilizes the JDBC driver to access data source and is the layer above JDBC. SQLJ translator is used to process SQLJ source files with the extension .sqlj. It translates .sqlj source files into .java files and an SQLJ serialized profile into a form of .ser file. The serialized file contains all the SQL statements in original SQLJ source file. The translated resulting .java file will contain calls to SQLJ run-time libraries in place of SQL statements. In order to bind the application statically to a DB2 database, you usethe DB2 profile customizer tool called db2 sqlj customize. The db2 sqlj customize connects and binds a package on the target database using the serialized profile.The package bound in the target database using db2 sqlj customize will contain sections which correspond to each SQL query in the serialized profile.
Commands associated with SQLJ:
sqlj is the translator that takes an embedded SQLJ program and creates a .ser file used for binding and a .java file that will also be compiled into byte code, as typical Java programs are compiled.
2. db2 sql jcustomize:
This command will take the .ser file from the sqlj step, connect to the database against which the application will be run, and bind four bind files for this application, all with different isolation levels.
3. db2 sql jbind:
This command can be used to rebind this application against other databases; for example, it can be used for moving the application from the test to the production database.
The following packages need to be imported for SQLJ:
A database security plan should define:
- Who is allowed access to the instance and/or database
- Where and how a user’s password will be verified
- The authority level that a user is granted
- The commands that a user is allowed to run
- The data that a user is allowed to read and/or alter
- The database objects a user is allowed to create, alter, and/or drop
There are three main mechanisms within DB2 that allow a DBA to implement a database security plan : authentication, authorization, and privileges.
Authentication is the first security feature you will encounter when you attempt to access a DB2 instance or database. DB2 authentication works closely with the security features of the underlying operating system to verify user IDs and passwords. DB2 can also work with security protocols like Kerberos to authenticate users.
Authorization involves determining the operations that users and/or groups can perform, and the data objects that they may access. A user’s ability to perform high-level database and instance management operations is determined by the authorities that they have been assigned. The five different authority levels within DB2 are SYSADM, SYSCTRL, SYSMAINT, DBADM, and LOAD.
Privileges are a bit more granular than authorities, and can be assigned to users and/or groups. Privileges help define the objects that a user can create or drop. They also define the commands that a user can use to access objects like tables, views, indexes, and packages.
You will need to create a database within the db2inst1 instance. Make sure that the db2instance variable is still set to db2inst1, and then create the sample database using the command db2sampldrive, using the name of the drive where you want to create the sample. For the examples, we’ll create the sample database on our C drive, as follows: