Some DBA tips

  • Be proactive. Don’t let your manager be blindsided in the hallway by some problem you’ve been working on. “You’d be surprised at people who are up all night fixing a problem and don’t tell anyone,” Floss said.
  • Be flexible. Are you willing to pitch in on anything? “My favorite person is the person who will do anything that needs to be done,” she said. “It’s also great résumé material. You get an opportunity to learn new databases, new operating systems.”
  • Find outside interests. Don’t let work be your world. “There are some people for whom work is their whole world,” Floss said. “We used to love those kinds of people. The problem is when work isn’t going so well, you can really tell. It helps to have somewhere else to funnel your energy.”
  • Have fun facts ready. It’s always a good thing to be able to articulate the size of your environment, whether it’s by database management systems, operating systems, gigabytes or terabytes, or the number of instances.
  • Make sure you are recoverable. Recovery is Job 1; make sure you can recover. “There’s testing, but what I’m finding now is [that] the fact that it works isn’t good enough,” Floss said. “Can you do it fast? Does it take three hours or 10 minutes? Because that’s system downtime.”
  • Highlight SOX vulnerabilities. Try to be friends with SOX auditors. If you know what the exposures are, you can fix them before auditors come in, or you can use SOX as clout to fix them. “It’s not a good thing if a SOX auditor comes in and lists 82 problems with your world, and you knew about 80 of them,” Floss said.
  • Balance quality versus time to deliver.
  • Understand the applications and provide value to them. “If customers were asked to give anonymous feedback, what would they say?” she asked. “Treat our app development peers as customers. Try to exceed their expectations at times.”
  • Sharpen Excel, Word and PowerPoint skills.

By decipherinfosysThis project needed to support having multiple NULL values in the column and still have a UNIQUE constraint. That is allowed by Oracle but not in SQL Server and DB2 LUW. There is a way to make this work in SQL Server and DB2 LUW also but that requires a work-around. Consider this table:


In this table, COL1 has been declared as the primary key but we want a UNIQUE constraint to be put on COL2 as well. Please note that COL2 is a nullable column and that SQL Server does not allow multiple NULL values in a UNIQUE index and treats them the same way. We can test it out prior to proceeding with the work-around:

Let us create a unique index first:


Now, let us try to insert these values:

insert into test_uq (col2) values (‘abc’);
insert into test_uq (col2) values (‘xyz’);
insert into test_uq (col2) values (Null);

All three will go in. After that, try to insert the NULL value again:

insert into test_uq (col2) values (Null);

and you will get the error:

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object ‘dbo.test_uq’ with unique index ‘TEST_UQ_IND_1′.
The statement has been terminated.

The work-around is to have a computed column and define the unique constraint on it. Here is how you can do that:

1) First, let’s drop the existing unique index:

drop index test_uq.TEST_UQ_IND_1

2) Next, let’s add the computed column:


In this command, we are stating that whenever the value for COL2 is null, replace it with the primary key after casting it to the same data-type as that of COL2. By doing so, we will mae sure that COL3 is always NOT NULL and always have unique values. This approach will work well in this case as there should never be a clash of the values between COL1 and COL2. For example, what-if you needed to do this on a column that was also an interger data-type column? In that case, chances of clashes of the data can arise. If you suspect a clash, you can have additional logic like: (CASE WHEN COL2 IS NULL then -1 * COL1 ELSE COL2 END). That way, you can still maintain the logic and the uniqueness.

3) Now, create the unique index on this column:


4) Next, let’s try to insert the NULL value again:

insert into test_uq (col2) values (Null);

This time it will go through. If we examine the contents of the table:

COL1        COL2       COL3 ———– ———- ———- 1           abc        abc 2           xyz        xyz 3           NULL       3 5           NULL       5

As you can see, we have allowed multiple NULL values now for COL2 and still maintained the uniqueness. We can next try to insert the value “abc” again and see if that preserves our uniqueness criteria:

insert into test_uq (col2) values (‘abc’);

This time, we will get an error:

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object ‘dbo.test_uq’ with unique index ‘TEST_UQ_IND_1′.
The statement has been terminated.

So, using this work-around, one can preserve the same behavior as Oracle. This might be useful to you as well in case you are working on a project that requires conversion from Oracle to SQL Server or Oracle to DB2 LUW.




User comments for the following queries are highly appreciated..

How does one compare data between 2 identical tables in DB2 ? In Oracle, one can use the minus option which is not there in DB2.

IBM (NYSE: IBM) today unveiled a new version of its Informix Dynamic Server (IDS) software designed to help companies better manage and utilize the deluge of data generated from global operations while increasing effectiveness and reducing data management costs by one-third.

The new IDS 11.5, code-named “Cheetah 2,” is tuned to enable customers to enhance business performance on cluster server environments using information management techniques inspired by IBM’s mainframe systems. As a result, IDS is the first non-mainframe data server to provide clustered data centers with low-cost continuous data availability and disaster recovery — regardless of geographic location or distance between backup data center sites.

“Global businesses of all sizes and across all industries are looking to gain a competitive advantage from the ability to execute uninterrupted business transactions locally and across their global enterprise,” said Inhi Cho, vice president, IBM Data Management Marketing. “The new version of IDS delivers the speed, agility and efficiency to help our clients enhance their overall business performance and reduce costs as their companies grow.”

IDS 11.5 features numerous enhancements that further its leading reliability and transaction performance, enabling users to reduce the number and cost of servers required. By enabling customers to manage the same amount of data with fewer hardware servers, IDS 11.5 requires fewer software licenses, lowers administration costs, and delivers energy and space savings.

When multiplied across the hundreds or thousands of applications or systems distributed throughout a business, IDS 11.5 offers tremendous savings and significantly lowers the cost for massively distributed data management. The versatile IDS 11.5 also benefits smaller companies and organizations that rely upon uninterrupted information access, but don’t have large IT staffs to manage numerous databases.

DB2 Truncate is not as straight forward as Oracle Truncate. If in Oracle you can use “truncate table “…  You may use the below command to truncate table in db2.

alter table schema.table_name activate not logged initially with empty table

IBM officials said the company’s database business, bolstered by last year’s releases of DB2 9.5 and IDS 11, enjoyed a solid first quarter of 2008.

With all the talk surrounding IBM’s successful first quarter for 2008, the company’s data server business could get lost in the shuffle. 

But the release of Big Blue’s DB2 9.5 database is progressing well, according to Bernie Spang, director of IBM data servers. Though he would not break out revenues by product, Spang said the company’s database business is seeing continued growth.

The company released DB2 9.5, code-named Viper 2, late last year with an emphasis on pure IBM database technology. IBM also released Informix Dynamic Server 11 (IDS 11) last June.

“We continue to see a healthy progress in partners choosing IBM and IBM database software over alternatives,” Spang said.

IBM’s Software Group recorded revenues of $4.85 billion, up 14 percent from the first quarter of 2007. Overall, the company posted total revenues of $24.5 billion, a jump of 11 percent during the same period.

IBM remains one of the top three database vendors. The other two are Oracle, which released the latest version of its database in August, and Microsoft, which plans to ship SQL Server 2008 in the third quarter. Microsoft officials have said they will use a combination of low pricing and a focus on productivity enhancements to compete against IBM and Oracle.

From a technology standpoint, IBM’s Viper 2 offers a choice for organizations that want support beyond Windows environments, he said.

“(Microsoft) is a Windows only answer,” he said. “I think the heterogeneous platform support that we offer will continue to be an advantage for us.”

Stepping beyond Viper 2 and IDS 11, the company also benefited from the acquisition of Cognos. IBM completed the Cognos buyout in January. Early this year, IBM began a major push around Cognos’ business intelligence (BI) products. Big Blue has already integrated Cognos’ technology and products into a number of offerings such as InfoSphere Warehouse, which Spang said has contributed to the success of the Software Group.

“It’s the execution on the information on-demand strategy across the elements of the portfolio individually, and the cumulative effect on our clients…that I think that’s a big part of what’s fueling the results you see,” he said.

Today’s tip: Don’t put DDS-defined logical file names in your SQL statements.

To understand why, let me give you a little background. When you execute an SQL command, the system determines the best way to carry out your request. That is, you concentrate on the task that needs to be done, and the system figures out how to do your task. Various software components are involved in this process, and for this discussion, you need to know about three of them.

First is the Query Dispatcher, whose job it is to decide which of the two query optimization engines it will call on to optimize and process a query. The second and third software components are the two query engines–the Classic Query Engine (CQE) and the SQL Query Engine (SQE). SQE is newer and better than CQE, but there are certain tasks that it can’t carry out.

You can reference four types of files in SQL statements: DDS-defined physical files, DDS-defined logical files, SQL tables, and SQL views. Guess which one of the four SQE can’t handle? I hope you guessed DDS-defined logical files, to which I will refer to simply as logical files in the following paragraphs. SQL views and indexes are also implemented as logical files, but they are not applicable to this discussion.

Not using any logical files in an SQL query is no guarantee that SQE will kick in and do the work. In V6R1, there are two other cases that force the CQE to handle a query. CQE continues to handle all non-SQL queries, such as the Open Query File (OPNQRYF) command and Query/400. CQE also handles distributed queries via DB2 Multisystem. In earlier releases, even more work is given to CQE.

If you wish to query a logical file from an SQL statement, consider querying the underlying physical file(s) instead. If the logical file has select/omit criteria, put the criteria in the WHERE clause. Another approach would be to create a view over the physical file and reference that view in your SQL query.