DB2 Miscellania

We (the current project I am working on) are in the process of migrating an existing ITIM database from Oracle 10g to DB2 9.1. This is still a work in progress, and something I want to write about simply because it hasn't been done (to the best of my knowledge). Not even the ITIM engineers I checked with have ever heard of a customer converting their database after the fact.

In any case, I have had to learn a little more about DB2 and one of the more useful tasks that I've had to learn is how to clear all of the records out of a table. My first attempt was to simply call  'DELETE FROM TABLE'. This may work for small tables, but does not work for larger tables, simply because DB2 has to keep a transaction log in case of a rollback. If the size of the table is larger than the size of your transaction logs this simply does not work.

As an alternative, you can use an import statement such as: 'IMPORT FROM /dev/null OF DEL REPLACE INTO TABLE'. This does an import of a comma delimiated (of del) file (in this case a non-existent empty file nul) and replaces the existing values in the named table. This is much faster than an unconditional delete because the transaction log is not involved.

Unfortunately, this technique did not work for me and resulted in SQL3201N errors. This was because DB2 does not allow values to be replaced during import if the table has referential integrity (foreign key) constraints. To bypass this you can do a load instead of an import: 'LOAD FROM /dev/null OF DEL REPLACE INTO TABLE'. This technique is equivalent to import without checking constraints, which puts the tables into a pending state if other tables have foreign key constraints against the table. To re-enable the integrity constraints and put the table back into normal state simply call: 'SET INTEGRITY FOR TABLE IMMEDIATE CHECKED'. This has to be done for the table loaded, and any table which has foreign key constraints against the table.

I felt this might be useful for non-DBAs since all of the related forum posts I could find related to this were from DBAs telling people to go read the DB2 manual. I read the manual...some of it!

Comments

Post a Comment

Popular posts from this blog

Load Balancing Web Services with WebSEAL

I came across this bit of information from an old email from several years ago, and wanted to capture it here for future reference. In case you're thinking of using a WebSEAL server as a proxy for a web service based application (SOAP or REST), there are some details you need to be aware of in how you tune your servers for a successful implementation. The advantage to using a WebSEAL server for proxying web service traffic is that you can enforce authorization based on the client ip address (typically a client web application). This isn't as strong of security as performing authenitcation and authorization using WS-Security or SAML, but if these options aren't available, WebSEAL can increase the security by only allowing certain ip addresses to access the application using POPs (Protected Object Policies). What you have to be aware of is the fact that the client opening the connection to the WebSEAL server is typically another application. This creates a problem if th...
Read more

Leveraging Azure MFA with CyberArk

Image
Posting this here for future reference. We had a customer that wanted to leverage Azure MFA for authenticating users (IT Administrators) to CyberArk for accessing servers and checking out privileged credentials. CyberArk has two mechanisms to support this: 1) RADIUS authentication or 2) SAML authentication. We initially attempted to implement RADIUS authentication using Microsoft's Network Policy Server with the Azure MFA plug-in but threw up the white flag after many hours of debugging network packet captures. In the end we implemented this use case using Azure SSO to provide SAML-based single sign-on to CyberArk. RADIUS authentication is a bit more "legacy"but has been the de facto protocol for step-up authentication use cases for many years. I won't get into the history of RADIUS but it has been around for a very long time (almost as long as the internet) and is widely used for network authentication such as to an IPSEC-based VPNs and 802.11X based Wireless network...
Read more

Role-based Identity Management Best Practices

Image
This is a white paper that I put together a few years ago for a large financial organization that was struggling with how to approach RBAC or role-based identity management without having the new AI and machine learning capabilities with some of the new IGA solutions such as SailPoint IdentityAI. Introduction Role-Based Identity Management and Role-Based Access Control (RBAC) has long been the nirvana of Identity & Access Management programs since the concept of distributed systems was introduced. The idea of aligning access needs to the business functions that users fulfill and streamline provisioning of that access has proved to be a constant challenge in terms of both IT operational efficiency as well as Compliance, Risk and Security needs. This paper aims to address the challenges for effectively managing role-based access and identify best practices to help organizations achieve better IT efficiency, reduce risk and increase security for their users, data and applications. Rol...
Read more