IBMDatabaseMagazine.com Blogs

The Information On Demand Conference Oct. 26 -31--with its opportunities for free certification--is less than two months away. If you haven't begun preparing for the certification exams, now is the right time to start.

Try the sample questions below, then review past questions here. All questions are from the book DB2 9 for Linux, UNIX, and Windows Database Administration: Certification Study Guide, published by MC Press and are reprinted with permission. Check back each week for more.

QUESTION 43
Which of the following database configuration parameters is used to force an application to wait indefinitely to obtain a lock on a table?

A. LOCKLIST
B. LOCKTIMEOUT
C. DLCHKTIME
D. MAXLOCKS

QUESTION 44
If the following CREATE TABLESPACE statement is executed:

CREATE REGULAR TABLESPACE sales_ts
PAGESIZE 8 K
MANAGED BY SYSTEM USING
(‘/mnt/data1’, ‘/mnt/data2’, ‘/mnt/data3’)
EXTENTSIZE 32
PREFETCHSIZE 128

How much space does DB2 allocate for the SALES_TS table space when all existing pages in the table space become full?

A. 8 KB
B. 32 KB
C. 128 KB
D. 256 KB

Continue reading "DB2 9 DBA certification sample questions: Table locks and table space allocation ..."

Comment on this blog entry
Topic(s):   DB2 Certification


Data Partitioning with Informix Dynamic Server

One of the engines that I manage contains a particularily nasty table. Let me know if this sounds familiar: This troublemaker is a history table that stores the details of each of the 2 million transactions our platform processes each day. The rows are wide, weighing in at over 900 bytes per row, letting me fit a whopping 2 rows per 2K page. The table contains the most recent six months of history; anything older than six months must be removed nightly to make room for tomorrow's data.

Without data partitioning this table could not exist.

Continue reading "Data Partitioning with Informix Dynamic Server..."

Comment on this blog entry
Topic(s):   Informix


DB2 9 DBA certification sample questions: Dual logging and LBAC protection

Get ready to take advantage of the free certification opportunities at the Information On Demand Conference Oct. 26 -31. Try the sample questions in this post, then try past questions here. All questions are from the book DB2 9 for Linux, UNIX, and Windows Database Administration: Certification Study Guide, published by MC Press and are reprinted with permission. Check back each week for more.

QUESTION 41
Which two of the following commands can be used to enable dual logging for a database named SAMPLE?

A. db2set DB2_NEWLOGPATH=D:\logs_copy
B. db2set DB2_NEWLOGPATH=1
C. UPDATE DB CFG FOR sample USING failarchpath D:\ logs_copy
D. UPDATE DB CFG FOR sample USING mirrorlogpath D:\ logs_copy
E. UPDATE DB CFG FOR sample USING logarchmeth2 MIRRORPATH: D:\ logs_copy

QUESTION 42
Which of the following SQL statements allows a user named USER1 to write to LBAC-protected columns that have been secured with a LBAC label that indicates a lower level of security than that held by USER1?

A. GRANT EXECPTION ON RULE DB2LBACWRITEARRAY WRITEDOWN FOR sec_policy TO USER user1
B. GRANT EXEMPTION ON RULE DB2LBACWRITEARRAY WRITEDOWN FOR sec_policy TO USER user1
C. GRANT EXECPTION ON RULE DB2LBACWRITEARRAY WRITEUP FOR sec_policy TO USER user1
D. GRANT EXEMPTION ON RULE DB2LBACWRITEARRAY WRITEUP FOR sec_policy TO USER user1

Continue reading "DB2 9 DBA certification sample questions: Dual logging and LBAC protection..."

Comment on this blog entry
Topic(s):   DB2 Certification


DB2 LUW Performance: Let the Elephant Hunt Begin

Hopefully you read the prior blog post on elephants and mosquitoes which discusses different approaches to statement performance analysis. If you haven't read it yet, please do so now. In this post, we will look at ways to hunt and kill your elephants. And, by no means do I favor cruelty to animals in any way, this is just a metaphor. Let us imagine that your phone just rang and your boss is screaming "What's happening RIGHT NOW?!?!?!!?!?" ...

Continue reading "DB2 LUW Performance: Let the Elephant Hunt Begin..."

Comments(2)
Topic(s):   DB2 for LUW performance


Informix security frameworks: Just how difficult is it to retro-fit "security"?

Everyone "knows" it's much easier to design and build a system that is secure, and that it's much more difficult to retrofit security to an existing environment. But how difficult is it really?

Continue reading "Informix security frameworks: Just how difficult is it to retro-fit "security"?..."

Comment on this blog entry
Topic(s):   Informix : Security


DB2 9 DBA Certification: Create and insert statements, plus dbmtrk

Try these two sample questions to help you prepare for the DB2 9 DBA certification exam. Then, try past questions here. All questions are from the book DB2 9 for Linux, UNIX, and Windows Database Administration: Certification Study Guide, published by MC Press and are reprinted with permission. Check back each week for more.

QUESTION 39
Given the following CREATE TABLE statement:

CREATE TABLE employee
(empid INTEGER NOT NULL,
lname VARVHAR(20),
gender CHAR(1) NOT NULL,
CONSTRAINT gender_ok CHECK(gender IN (‘M’, ‘F’)) NOT ENFORCED
ENABLE QUERY OPTIMIZATION)

And the following INSERT statement:

INSERT INTO employee VALUES (1, ‘Smith’, ‘M’), (2, ‘Doe’, ‘F’), (3, ‘Jones’, ‘U’)

Which of the following queries will return an empty result set?

A. SELECT COUNT(gender) FROM employee
B. SELECT * FROM employee WHERE gender = ‘M’
C. SELECT * FROM employee WHERE gender = ‘F’
D. SELECT * FROM employee WHERE gender = ‘U’

QUESTION 40
Which of the following best describes the functionality of db2mtrk?

A. It reports how memory is being managed by the Self Tuning Memory Manager.
B. It estimates the memory requirements for a database, based on values assigned to the memory-related Database Manager configuration parameters.
C. It produces report of memory status for instances, databases and agents.
D. It recommends memory-related Database Manager configuration parameter values that will improve memory utilization.

Continue reading "DB2 9 DBA Certification: Create and insert statements, plus dbmtrk..."

Comments(1)
Topic(s):   DB2 Certification


The Informix High Performance Loader - Part 3

Welcome to the third and final installment of the Informix HPL blog series (at least for now.) In parts 1 and 2 we abandoned the GUI and went old school command line with onpladm (that's the way it was and weeeeeeeee liked it!), we unloaded and loaded lots of data to and from a flat file on disk, we moved data from one table to another in parallel using named pipes and we even moved data between servers without using flat files. This toolset should be enough to cover most of what you would ever need to do with HPL. If not, it will at least give you a solid foundataion to start with. In my final attempt to increase HPL awareness I want to take a look at the more tedious, but sometimes necessary, HPL maps and formats objects.

Continue reading "The Informix High Performance Loader - Part 3..."

Comment on this blog entry
Topic(s):   Informix


DB2 LUW Performance: Statement Analysis Introduction - Hunting Elephants and Mosquitoes

In the prior blog post, we learned how to determine if your database is CPU bound, lock bound, sort bound, or I/O bound, and how to determine if a performance problem is attributable to the database or not. We will now turn our attention to statement analysis methodologies so that we can discover the sources of bottlenecks. “Statements” is broadly defined to include both classic SQL and newer XML queries.

Continue reading "DB2 LUW Performance: Statement Analysis Introduction - Hunting Elephants and Mosquitoes..."

Comments(1)
Topic(s):   DB2 for LUW performance


The Informix High Performance Loader - Part 2

Last month we explored some of the basic, but powerful, funtionality of the Informix High Performance Loader (HPL) and the CLI configuration utility onpladm. Today we will be looking at some advanced uses of HPL that will allow you to simultaneously unload and load data without the need for large intermediate data files.

Continue reading "The Informix High Performance Loader - Part 2..."

Comments(2)
Topic(s):   Informix


DB2 LUW Performance: The DNA Test of Performance Accountability

The database is often presumed guilty if there is a performance issue. But your database seems fine; all the symptoms of good performance are present, so now how do you prove your database’s innocence?

In the prior blog post, I provided a checklist of some metrics that would help you assemble your defense if it was asserted that your database was the source of performance problems. But those ratios and indicators are just circumstantial evidence of probable innocence. Here comes the DNA test. It’s hard. It’s time consuming. It’s complex. But the analytical effort just might help get you out of the hot seat and properly direct a performance issue to application or networking teams...

Continue reading "DB2 LUW Performance: The DNA Test of Performance Accountability..."

Comments(5)
Topic(s):   DB2 for LUW performance


Informix basks in the iPhone's glow

With Apple's latest announcements making headlines (hear about the cheaper iPhone, anyone?), it's worth mentioning Informix Dynamic Server's Mac OS X support.

Continue reading "Informix basks in the iPhone's glow..."

Comment on this blog entry
Topic(s):   Informix


InformationWeek helps celebrate DB2's 25th birthday

Stop by InformationWeek.com to read the tale of DB2's rags-to-riches journey from RDBMS newcomer to its current spot as a market leader.

Continue reading "InformationWeek helps celebrate DB2's 25th birthday..."

Comments(1)
Topic(s):   DB2 (all platforms)


DB2 9 DBA Certification: Changing the SORTHEAP during access; creating storage tablespaces

Here are two more sample questions to help you prepare for the DB2 9 DBA certification exam. Give these a try, then try past questions here. All questions are from the book DB2 9 for Linux, UNIX, and Windows Database Administration: Certification Study Guide, published by MC Press and are reprinted with permission. Check back each week for more.

QUESTION 37

A database administrator successfully changes the value of the SORTHEAP database configuration parameter while a running application is accessing the database. When will the application see the effects?

A. When a new SQL statement is executed.
B. When a new transaction is started.
C. When a new database connection is established.
D. When the application terminates and is restarted.

QUESTION 38

Given the following CREATE TABLESPACE statement:

CREATE REGULAR TABLESPACE payroll_ts
MANAGED BY AUTOMATIC STORAGE
EXTENTSIZE 32
PREFETCHSIZE 128

Which of the following statements is NOT true?

A. When created, the PAYROLL_TS table space will be a DMS table space with file containers.
B. The database the PAYROLL_TS table space is to be created for must be enabled for automatic storage.
C. When created, the PAYROLL_TS table space will be an SMS table space with directory containers.
D. The MANAGED BY AUTOMATIC STORAGE clause is unnecessary and could have been left out of the CREATE TABLESPACE command.

Continue reading "DB2 9 DBA Certification: Changing the SORTHEAP during access; creating storage tablespaces..."

Comments(3)
Topic(s):   DB2 Certification


Celebrate DB2, U2, IMS, and IDS anniversaries by helping write their histories

During this week 25 years ago (June 7, specifically) DB2 made its debut (check out the original press release for a trip down memory lane) on the mainframe. As storied as its long history is, DB2 isn't the only IBM database technology celebrating a milestone this year. It's joined by UniData and IMS (20 and 40 years, respectively). Of course, although they're not celebrating anniversaries that end in 5 or 0 this year, Informix (27 years) and UniVerse (23 years), certainly have eventful histories.

Now that the IBM Database Magazine wiki is back from a brief cloud-induced hiatus (so long, StikiPad), you're invited to help document the achievements of your favorite IBM database technologies in its pages.

Continue reading "Celebrate DB2, U2, IMS, and IDS anniversaries by helping write their histories..."

Comment on this blog entry
Topic(s):   DB2 (all platforms) : Informix


DB2 LUW Performance: DB2 is ALIVE and WELL and IT’S NOT YOUR FAULT!

It is an unfortunate reality that the database and the DBA are too often presumed guilty by default. Everyone tends to want to blame the database first, even though performance degradation could be caused by network problems, storage problems, the Web server, sun spots, or poor application coding. So, as a database professional, how do you get yourself out of the hot seat and prove your database’s innocence? Here's a checklist to assemble your defense:

Continue reading "DB2 LUW Performance: DB2 is ALIVE and WELL and IT’S NOT YOUR FAULT!..."

Comment on this blog entry
Topic(s):   DB2 for LUW performance


DB2 Certification: Restoring a database and understanding database privileges

Brush up for certification by glancing through these sample questions. All questions are from the book DB2 9 for Linux, UNIX, and Windows Database Administration: Certification Study Guide, published by MC Press, and are reprinted with permission. Check back each week for more; browse past questions here.

QUESTION 35
Which of the following commands will restore a database using information found in the recovery history log file?

A. RESTART DATABASE
B. RESTORE DATABASE
C. RECOVER DATABASE
D. REBUILD DATABASE

QUESTION 36
Which of the following database privileges are NOT automatically granted to the group PUBLIC when a database is created?

A. CONNECT
B. BINDADD
C. IMPLICIT_SCHEMA
D. CREATE_EXTERNAL_ROUTINE

Continue reading "DB2 Certification: Restoring a database and understanding database privileges..."

Comment on this blog entry
Topic(s):   DB2 Certification


The Informix High Performance Loader – Part 1

The High Performance Loader is the absolute best utility available when it comes to inserting or unloading large amounts of data from your systems. Up until version 9.4 you had two options if you wanted to configure an HPL load or unload job. You could use the ipload GUI or you could reverse engineer the steps required to correctly populate the onpload database.

Continue reading "The Informix High Performance Loader – Part 1..."

Comments(1)
Topic(s):   Informix


DB2 for Linux, UNIX, and Windows Versions 8, 9.1 and 9.5 Security Vulnerabilities and HIPER APARs

Did you catch the 21 April 2008 IBM Flash Alert? Since not everyone subscribes to IBM alerts, I thought it prudent to bring your attention to new fixpacks here. It seems IBM acted reasonably swiftly and appropriately to create these new fixpacks for V8, V9, and V9.5, which address the documented vulnerabilities.

Continue reading "DB2 for Linux, UNIX, and Windows Versions 8, 9.1 and 9.5 Security Vulnerabilities and HIPER APARs..."

Comment on this blog entry
Topic(s):   Security


DB2 Certification: Creating objects from the Control Center and understanding Import command options

IDUG North America and the free certification opportunities it presents are just weeks away. Brush up by glancing through these certification sample questions and many others. All questions are from the book DB2 9 for Linux, UNIX, and Windows Database Administration: Certification Study Guide, published by MC Press, and are reprinted with permission. Check back each week for more.

QUESTION 33
Which of the following objects can NOT be created from the Control Center?

A. Buffer pool
B. Event monitor
C. Trigger
D. User-defined Function

QUESTION 34
Which of the following IMPORT command options will cause the Import utility to acquire a table-level lock after every commit?

A. ALLOW NO ACCESS
B. ALLOW READ ACCESS
C. ALLOW WRITE ACCESS
D. ALLOW FULL ACCESS

Continue reading "DB2 Certification: Creating objects from the Control Center and understanding Import command options..."

Comment on this blog entry
Topic(s):   DB2 Certification


Finding the Right Page Size for Your Indexes on Informix

In my last blog we took a look at storing indexes in a dbspace with a large page size to try to decrease the number of levels in an index and increase performance by reducing the number of I/Os required to traverse the index. Using the largest page size available, 16K, we were able to decrease the number of levels in our test index from 5 to 4, but only realized a slight performance gain if--and only if--we increased our resident segment (buffers) from 512 MB to 1.5 GB to compensate for the larger, less selective page size and larger cache miss penalty of a 16K disk read vs a 2K disk read. I ended the blog with the suggestion that the best solution would be to find the smallest page size that still reduces the number of levels, giving us the benefits of better caching through smaller index pages and the benefits of reduced index levels when using larger page sizes.

Continue reading "Finding the Right Page Size for Your Indexes on Informix..."

Comments(2)
Topic(s):   Informix


IIUG Power Conference Update: Winds of Change

The first International Informix User Group conference since the IBM acquisition in 2001 kicked off this morning with a keynote by Dr. Ambuj Goyal, general manager of IBM Information Management and self-described doting parent to many RDBMSs, each with its own successful market.

Continue reading "IIUG Power Conference Update: Winds of Change..."

Comment on this blog entry
Topic(s):   Informix


DB2 Certification: Making configuration changes and executing the create table command

Keep up your certification preparation with this new set of sample questions, which originally appeared in the book DB2 9 for Linux, UNIX, and Windows Database Administration: Certification Study Guide, published by MC Press. All questions are reprinted with permission. Check back each week for more, and try past questions here.

QUESTION 31
Which of the following commands will recommend and make configuration changes for an instance named DB2INST1?

A. AUTOCONFIGURE USING db2inst1 APPLY
B. AUTOCONFIGURE USING mem_percent 60 APPLY db2inst1
C. AUTOCONFIGURE USING mem_percent 60 APPLY DBM ONLY
D. AUTOCONFIGURE USING mem_percent 60 APPLY DB AND DBM

QUESTION 32
If the following CREATE DATABASE command is executed:

CREATE DATABASE sales ON /mnt/data1, /mnt/data2
COLLATE USING IDENTITY
CATALOG TABLESPACE MANAGED BY SYSTEM USING (‘mnt/syscat’);

Which of the following statements is NOT true about the resulting database?

A. Automatic storage is enabled for the database
B. An SMS table space will be used to hold the system catalog
C. User data will be stored on /mnt/data1 and /mnt/data2
D. Metadata for the database will be stored on /mnt/data2

Continue reading "DB2 Certification: Making configuration changes and executing the create table command..."

Comment on this blog entry
Topic(s):   DB2 Certification


DB2 LUW Performance: Write I/O Optimization Part 2

It's said that there is more than one way to skin a cat, meaning there are multiple ways to accomplish the same objective. As for the saying, I don't think this is very kind to cats even though I prefer dogs. The prior blog post discussed making adjustments to CHNGPGS_THRESH to reduce, avoid, or mitigate transient "brown outs" in transaction throughput. There is another way...

Continue reading "DB2 LUW Performance: Write I/O Optimization Part 2..."

Comments(2)
Topic(s):   DB2 for LUW performance


A Web 2.0 Expo debate: creativity vs. control in the enterprise

Forrester Research is projecting that companies will spend nearly $5 billion on Web 2.0 technologies like mashups, wikis, widgets, and so on in the next five years. That explains the tremendous energy around the Web 2.0 Expo underway in San Francisco this week.

Tomorrow Anant Jhingran, CTO of Information Management, and RedMonk analyst Stephen O'Grady face off over the challenges of bringing Web 2.0 technologies into the enterprise in a session called "Creativity vs. Control: The Debate Continues—Can Enterprises Have Both?"

Continue reading "A Web 2.0 Expo debate: creativity vs. control in the enterprise..."

Comment on this blog entry
Topic(s):   DB2 (all platforms) : Information Management in Context : Informix : Security


Configurable Page Size (CPS) and Informix

In version 10.0 of Informix Dynamic Server we were given the ability configure the page size of the data stored in a dbspace. For my Linux-based servers this means I have the option to store data and index pages on 2K, 4K, 6K, 8K, 10K, 12K, 14K and 16K pages instead of the previous option of just 2K pages. That's all fine and dandy, but how can we put this to good use?

Continue reading "Configurable Page Size (CPS) and Informix..."

Comment on this blog entry
Topic(s):   Informix


DB2 DBA Certification Sample Questions: Backing up a database without affecting workloads; understanding privileges

As conference season approaches, it's time to step up your studying. Try these sample questions to help you prepare for the DB2 9 DBA certificiation exam. The questions originally appeared in the book DB2 9 for Linux, UNIX, and Windows Database Administration: Certification Study Guide, published by MC Press, and are reprinted with permission. Check back each week for more, and try past questions here.

QUESTION 29
Which of the following commands can be used to backup a database named PAYROLL, in such a way that workloads against the database are not impacted by more than 25%?

A. BACKUP DATABASE payroll ONLINE TO D:\backups UTIL_IMPACT_PRIORITY 25
B. BACKUP DATABASE payroll ONLINE TO D:\backups UTIL_IMPACT_LIM 25
C. BACKUP DATABASE payroll ONLINE TO D:\backups UTIL_IMPACT_PRIORITY 75
D. BACKUP DATABASE payroll ONLINE TO D:\backups UTIL_IMPACT_PRIORITY 75

QUESTION 30
A user named USER1 is granted DBADM authority. Assuming no other authorities/privileges have been granted and all privileges have been revoked from the group PUBLIC, if the following SQL statement is executed:

REVOKE DBADM ON DATABASE FROM user1

What authorities/privileges will user USER1 have?

A. None
B. CONNECT
C. SYSCTRL
D. CREATETAB

Continue reading "DB2 DBA Certification Sample Questions: Backing up a database without affecting workloads; understanding privileges..."

Comments(5)
Topic(s):   DB2 Certification


DB2 LUW Performance: Write I/O Optimization

A student who took this class last week at WDUG emailed me the day after to thank me. He said he enjoyed the class and successfully reduced the elapsed time of a troublesome query from two hours to three minutes! If you missed the WDUG session, I hope you will be able to join my Ed Seminar at IDUG "DB2 LUW Performance Diagnosis Learning Lab". This post covers topics related to optimizing Write I/O performance.

Continue reading "DB2 LUW Performance: Write I/O Optimization..."

Comment on this blog entry
Topic(s):   DB2 for LUW performance


DB2 DBA Certification Sample Questions: Using the Tools Settings notebook and deleting all records from a specific table

Here are more sample questions to help you prepare for the DB2 9 DBA certificiation exam. The questions originally appeared in the book DB2 9 for Linux, UNIX, and Windows Database Administration: Certification Study Guide, published by MC Press, and are reprinted with permission. Check back each week for more, and try past questions here.

QUESTION 27
Which of the following operations can NOT be performed using the Tools Settings notebook?

A. Define replication environments.
B. Change the font used to display text and menu options.
C. Set the default scheduling scheme.
D. Configure Health Status Beacons.

QUESTION 28
A database administrator wants to delete all records found in a table named DEPARTMENT. If the DEPARTMENT table resides in a database that is stored on an AIX server, which of the following commands can be used to accomplish this objective?

A. LOAD FROM null OF DEL INSERT INTO department
B. LOAD FROM null OF DEL REPLACE INTO department
C. LOAD FROM /dev/null OF DEL INSERT INTO department
D. LOAD FROM /dev/null OF DEL REPLACE INTO department

Continue reading "DB2 DBA Certification Sample Questions: Using the Tools Settings notebook and deleting all records from a specific table..."

Comments(7)
Topic(s):   DB2 Certification


Writing a Simple Informix Engine Benchmark in Python

The kind people at IBM Database Magazine have asked me to join their highly unpaid blogging staff to write a few paragraphs a month on all things Informix. I'll be pulling from my own day to day experiences as a DBA who frequently puts on the software developer, system administrator, database architect and storage administrator hats for content. Today I'll be wearing the software developer and DBA hats to write a quick and easy python script to hammer our database engine to get a rough idea of what kind of load our servers can handle.

Continue reading "Writing a Simple Informix Engine Benchmark in Python..."

Comments(3)
Topic(s):   Informix


DB2 LUW Performance: Table Read I/O and Overflows

Understanding Table I/O performance is critically important to properly diagnosing the health and efficiency of a database and pinpointing problems. In fact, if I only had just a few minutes to quickly assess a database, I'd look at 3 key measurements...

Continue reading "DB2 LUW Performance: Table Read I/O and Overflows..."

Comments(4)
Topic(s):   DB2 for LUW performance


Will declaring things dead ever die?

Earlier this week, I saw this New York Times article about the surprising (to some) longevity of the mainframe, a technology declared dead in the early 90s yet which continues to have a healthy and robust life. Some have said the very declaration was a plant on the part of PC makers and champions. I made light of the mainframe’s undead status last year (and readers played along by declaring it their favorite undead technology.

Continue reading "Will declaring things dead ever die?..."

Comments(3)
Topic(s):   DB2 (all platforms)


DB2 DBA Certification Sample Questions: Configuring server communications and understanding SMS tablespaces

As the 20th IDUG convention nears, you might be considering taking advantage of the free certification exams that will be offered there. If you're considering the DB2 9 DBA certificiation exam, try your hand at these sample certification questions from the book DB2 9 for Linux, UNIX, and Windows Database Administration: Certification Study Guide, published by MC Press and reprinted with permission. Check back each week for more, and try past questions here.

Given the following information about a DB2 server:

Instance name: db2inst1
Port number: 60000
Service name: db2c_db2inst1
Host name: db2host
Host TCP/IP address: 10.205.15.100
Protocol: TCP/IP
Database name: PROD_DB

Assuming the following entry has been made to the services file:

db2c_db2inst1 60000/tcp

Which two of the following commands must be executed to correctly configure communications for the server?

A. UPDATE DBM CFG USING SVCENAME db2c_db2inst1
B. UPDATE DBM CFG USING SVCEPORT 60000
C. db2set DB2COMM=TCPIP
D. UPDATE DBM CFG USING NNAME db2host
E. db2set DB2COMM=TCP/IP

QUESTION 26
Which of the following is NOT a true statement about SMS table spaces?

A. Regular data and long data cannot be split across multiple table spaces.
B. Storage space is allocated by the operating system as it is needed.
C. Containers can be added to or deleted from existing table spaces using the ALTER TABLESPACE command.
D. Only directory containers can be used for storage; file and device containers cannot be used.

Continue reading "DB2 DBA Certification Sample Questions: Configuring server communications and understanding SMS tablespaces..."

Comment on this blog entry
Topic(s):   DB2 Certification


DB2 LUW Performance: The Death of DB2

Disclaimer up front: This contains a rant, but it may be relevant to the performance of your organization or your databases. No children or animals were harmed in the writing of this blog. In a departure from my 21 prior posts, there are no formulas herein. I'm just going to vent about events in our industry. Feel free to skip to the next blog post or post a comment if you are so inclined.

Continue reading "DB2 LUW Performance: The Death of DB2..."

Comments(12)
Topic(s):   DB2 for LUW performance


DB2 9 for Linux, UNIX, and Windows DBA Certification Exam Sample Questions

Spring is here, and daylight is lasting longer. Why not use the extra time to prepare for the DB2 9 DBA certificiation exam? Here are more sample certification questions from the book DB2 9 for Linux, UNIX, and Windows Database Administration: Certification Study Guide, published by MC Press and reprinted with permission. Check back each week for more, and try past questions here.

QUESTION 23
The following command was executed in an attempt to return a database named SAMPLE to the state it was in at 9:30 AM on July 1, 2007:

ROLLFORWARD DB sample TO 2007-07-01-09.30.00.000000 AND STOP

During execution, the following error was returned:

SQL4970N Roll-forward recovery on database SAMPLE cannot reach the specified stop point (end-of-log or point-in-time) because of missing or corrupted log file(s) on database partition(s) “0”. Roll-forward recovery processing has halted on log file S0000007.LOG.

Which of the following options can be added to the ROLLFORWARD command to resolve the problem?

A. END OF LOGS
B. OVERFLOW LOG PATH
C. ALTERNATE LOG PATH
D. MIRROR LOG PATH

QUESTION 24
User USER1 needs to remove a view named ORDERS_V, which is based on a table named ORDERS, from the SALES database. Assuming user USER1 does not hold any privileges, which of the following privileges must be granted before user USER1 will be allowed to drop the view?

A. DROP privilege on the ORDERS_V view
B. CONTROL privilege on the ORDERS table
C. DROP privilege on the ORDERS_V view
D. CONTROL privilege on the ORDERS_V view

Continue reading "DB2 9 for Linux, UNIX, and Windows DBA Certification Exam Sample Questions..."

Comment on this blog entry
Topic(s):   DB2 Certification


DB2 LUW Performance: Direct I/O Times

Returning our attention to the question "Where does the time go?", we need to look at Direct I/O times. Direct I/O is I/O that occurs directly to disk without an intermediate visit or presence in the Bufferpools. Direct I/O is used by DB2 in support of LONG and LOB objects. Even if you think you are not using LONG and LOB objects, you are implicitly using them as these data types are found throughout the DB2 catalog.

Continue reading "DB2 LUW Performance: Direct I/O Times..."

Comments(6)
Topic(s):   DB2 for LUW performance


DB2 9 DBA Certification Exam: DB2 Indexes and Create Table Statements

Here are more questions from the book DB2 9 for Linux, UNIX, and Windows Database Administration: Certification Study Guide, published by MC Press and reprinted with permission. To prepare for the DB2 9 Database Administration exam, try these questions. Check back each week for more. In the meantime, you can and try past questions here.

QUESTION 21
Which two of the following are valid indexes that are supported by DB2?

A. Spatial grid indexes
B. Static bitmap indexes
C. Dynamic bitmap indexes
D. Page-based indexes
E. Multidimensional range-clustered indexes

QUESTION 22
The table EMPLOYEES was created by executing the following command:

CREATE TABLE employees (empid INTEGER NOT NULL PRIMARY KEY,
name VARCHAR(25))

Assuming the file EMPDATA.DEL contains the following data:

100, “Kim Moutsos”
100, “Dwaine Snow”
200, “Rebecca Bond”
300, “Phil Gunning”
400, “Paul Zikopoulos”

If the following commands are executed:

CREATE TABLE emp_exp LIKE EMPLOYEES;
LOAD FROM empdata.del OF DEL
INSERT INTO EMPLOYEES
FOR EXCEPTION emp_exp;

Which of the following statements is true?

A. The table EMPLOYEES will contain four rows; the table EMP_EXP will be empty.
B. The table EMPLOYEES will contain five rows; the table EMP_EXP will contain five rows.
C. The table EMPLOYEES will contain four rows; the table EMP_EXP will con-tain one row.
D. The table EMPLOYEES will contain five rows; the table EMP_EXP will contain one row.

Continue reading "DB2 9 DBA Certification Exam: DB2 Indexes and Create Table Statements..."

Comments(4)
Topic(s):   DB2 Certification


DB2 9 DBA Certification Exam: DB2 Discovery and index keys on XML content

As conference season approaches (IDUG North America is coming up in May), you might be starting to think about taking advantage of the free certification exams offered at these shows. These questions, reprinted with permission from the book DB2 9 for Linux, UNIX, and Windows Database Administration: Certification Study Guide, published by MC Press, should help you prepare. Check back each week for more questions, and try past questions here.

QUESTION 19
A database server has one instance named DB2INST1 and two databases named SALES and PAYROLL. Which of the following commands will allow the DB2INST1 instance and the SALES database, but prevent the PAYROLL database from being seen by DB2 Discovery?

A. UPDATE DBM CFG USING DISCOVER_INST SEARCH;
UPDATE DB CFG FOR sales DISCOVER_DB SEARCH;
UPDATE DB CFG FOR payroll USING DISCOVER_DB DISABLE;
B. UPDATE DBM CFG USING DISCOVER_INST ENABLE;
UPDATE DB CFG FOR sales DISCOVER_DB ENABLE;
UPDATE DB CFG FOR payroll USING DISCOVER_DB DISABLE;
C. UPDATE DBM CFG USING DISCOVERY SEARCH;
UPDATE DB CFG FOR sales DISCOVER_DB ENABLE;
UPDATE DB CFG FOR payroll USING DISCOVER_DB DISABLE;
D. UPDATE DBM CFG USING DISCOVERY ENABLE;
UPDATE DB CFG FOR sales DISCOVER_DB ENABLE;
UPDATE DB CFG FOR payroll USING DISCOVER_DB DISABLE;

QUESTION 20
If the following CREATE INDEX statements are executed:

CREATE INDEX cust_zip_idx ON customer(custinfo)
GENERATE KEY
USING XMLPATTERN '/customerinfo/addr/zip-pcode'
AS SQL DOUBLE;

CREATE INDEX cust_city_idx ON customer(custinfo)
GENERATE KEY
USING XMLPATTERN '/customerinfo/addr/city'
AS SQL VARCHAR(40);

And the following XML documents are inserted into the CUSTOMER table:

<?xml version="1.0" encoding="UTF-8" ?>
<customerinfo xmlns="http://crecord.dat. id="1000">
<name>John Doe</name>
<addr country="United States">
<street>25 East Creek Drive</street>
<city>Raleigh</city>
<state-prov>North Carolina</state-prov>
<zip-pcode>27603</zip-pcode>
</addr>
<phone type="work">919-555-1212</phone>
<email>john.doe@yahoo.com</email>
</customerinfo>

<?xml version="1.0" encoding="UTF-8" ?>
<customerinfo xmlns="http://crecord.dat. id="1010">
<name>Jane Smith</name>
<addr country="United States">
<street>2120 Stewart Street</street>
<city></city>
<state-prov>South Carolina</state-prov>
<zip-pcode>29501</zip-pcode>
</addr>
<phone type="work">843-555-3434</phone>
<email>jane.smith@aol.com</email>
</customerinfo>

How many index keys will be generated?

A. 1
B. 2
C. 3
D. 4

Continue reading "DB2 9 DBA Certification Exam: DB2 Discovery and index keys on XML content ..."

Comment on this blog entry
Topic(s):   DB2 Certification


DB2 LUW Performance: More on Locks

First, my apologies for being away from the blog keyboard for so long. Kim Moutsos actually contacted me to see if I was still alive. Truth be known, my grandmother died, my father is in the hospital battling cancer, and I've been traveling the US States quite a bit helping companies save millions in software and hardware costs. Nonetheless, here's a quickie on some lock formulas and other updates. The good news is, I suppose, I'm accumulating a great deal of new material to share with you in future posts.

Continue reading "DB2 LUW Performance: More on Locks..."

Comment on this blog entry
Topic(s):   DB2 for LUW performance


DB2 DBA asks: Is database administrator the right title for the job?

In his new DB2 DBA column in Issue 1 of IBM Database Magazine, Robert Catterall asks whether the title “database administrator” really describes the work that DBAs do. And, does it reflect the work that’s truly valuable to a business? Here’s how he sums up the question:

Continue reading "DB2 DBA asks: Is database administrator the right title for the job? ..."

Comments(1)
Topic(s):   DB2 (all platforms)


DB2 9 DBA Certification Exam: Logging index operations for recovery, and package privileges

Here are more sample certification questions from the book DB2 9 for Linux, UNIX, and Windows Database Administration: Certification Study Guide, published by MC Press. The questions are reprinted with permission. Check back each week for more, and try past questions here.

QUESTION 17
Which of the following commands will ensure that index creation, recreation, and reorganization operations against a database named SAMPLE will be logged so that indexes can be reconstructed during roll-forward recovery operations or high availability disaster recovery (HADR) log replay procedures?

A. UPDATE DB CFG FOR sample USING INDEXREC ON
B. UPDATE DB CFG FOR sample USING LOGINDEXMAINT ON
C. UPDATE DB CFG FOR sample USING INDEXOPS ON
D. UPDATE DB CFG FOR sample USING LOGINDEXBUILD ON

QUESTION 18
Which of the following privileges allows a user to generate a package for an embedded SQL application and store it in a database?

A. BIND
B. BINDADD
C. CREATE_EXTERNAL_ROUTINE
D. CREATE_NOT_FENCED_ROUTINE

Continue reading "DB2 9 DBA Certification Exam: Logging index operations for recovery, and package privileges..."

Comments(1)
Topic(s):   DB2 Certification


DB2 Magazine is now IBM Database Magazine

Later today, you'll notice that DB2mag.com is redirecting to ibmdatabasemag.com, and print subscribers will soon get IBM Database Magazine in the mail. What's going on?

Continue reading "DB2 Magazine is now IBM Database Magazine..."

Comments(2)
Topic(s):   DB2 (all platforms)


DB2 9 Certification Study Questions: All about indexes

Try your hand at these questions from the book DB2 9 for Linux, UNIX, and Windows Database Administration: Certification Study Guide, published by MC Press. The questions are reprinted with permission. Check back each week for more, and try past questions here.

QUESTION 15
Given the following CREATE TABLE statement:

CREATE TABLE tab1
(c1 SMALLINT,
c2 CHAR(4))

Table TAB1 is accessed frequently by two applications; one application returns the data in column C1 in ascending order, the other application returns the data in column C1 in des-cending order. To improve performance, a database administrator executes the following commands:

CREATE INDEX indx1 ON tab1(c1 ASC);
CREATE INDEX indx2 ON tab1(c1 DESC);

Which of the following will occur?

A. Index INDX1 will be created and the first application will use it when retrieving data; the index INDX2 will be created and the second application will use it when retrieving data.
B. Index INDX1 will be created. When an attempt is made to create index INDX2, DB2 will alter index INDX1 to allow reverse scans and index INDX1 be used by both applications when retrieving data.
C. Index INDX1 will be created. When an attempt is made to create index INDX2, DB2 will alter index INDX2 to allow reverse scans and drop index INDX1; index INDX2 will be used by both applications when retrieving data.
D. Index INDX1 will be created and allow reverse scans by default. When an at-tempt is made to create index INDX2, DB2 will issue a duplicate index warn-ing message. Index INDX1 be used by both applications when retrieving data.

QUESTION 16
In an attempt to improve query performance, a database administrator created an index for a table named EMPLOYEE, which contains 500,000 records. Performance of ad-hoc queries ran against the EMPLOYEE table has improved, but performance of a batch application that runs at night has not improved. Which of the following operations should correct this problem?

A. REORGCHK
B. REORG
C. FLUSH PACKAGE CACHE
D. REBIND

Continue reading "DB2 9 Certification Study Questions: All about indexes..."

Comment on this blog entry
Topic(s):  


DB2 9 Certification Study Questions: DB2 diagnostics log file and data row compression

Here are more questions from the book DB2 9 for Linux, UNIX, and Windows Database Administration: Certification Study Guide, published by MC Press, to help you prepare for the certification test. The questions are reprinted with permission. Check back each week for more, and try past questions here.

QUESTION 13
Which of the following statements about the DB2 Diagnostics Log File is NOT true?

A. Once created, the DB2 Diagnostics Log File grows continuously.
B. If the DB2 Diagnostics Log File is deleted, a new one will be created.
C. The db2diag utility can be used to filter and format the information stored in the DB2 Diagnostics Log File.
D. If the DB2DIAG_RESTART DB2 Database Manager configuration parameter is set to YES, the DB2 Diagnostics Log File will be deleted and recreated whenever an instance is restarted.

QUESTION 14
Which of the following statements is NOT valid when discussing data row compression?

A. Data row compression can lead to disk I/O savings and improved buffer pool hit ratios.
B. Compressing data at the row level is advantageous because it allows repeating patterns that span multiple columns within a row to be replaced with shorter symbols.
C. Data row compression for a table can be enabled by executing the ALTER TABLE statement with the COMPRESS YES option specified.
D. Only data in a table enabled for data row compression is compressed; data in corresponding indexes and transaction logs is not compressed.

Continue reading "DB2 9 Certification Study Questions: DB2 diagnostics log file and data row compression..."

Comment on this blog entry
Topic(s):   DB2 Certification


DB2 LUW Performance: Fighting Over Data - LOCKS

Every once in a while I hear a DBA say they are having Lock problems. Since read-only or read-mostly Data Warehouse databases rarely have lock problems, I quickly assume they have an OLTP database. It is my opinion that locks are rarely, if ever, a PROBLEM. Locks are a SYMPTOM of another very real problem.

Continue reading "DB2 LUW Performance: Fighting Over Data - LOCKS..."

Comments(2)
Topic(s):   DB2 for LUW performance


Y2K38 and DB2, Is Your Application Safe?

This Saturday marks 30 years to the “Unix Millennium Bug” which is where it has been 2^31 seconds since UNIX epoch. This is when the standard signed int overflows its time representation. 0111 1111 1111 1111 1111 1111 1111 1110 changes to 1000 0000 0000 0000 0000 0000 0000 0000 which is negative, as a integer uses the last bit for positive/negative. So why should we start to caring about it now?

Continue reading " Y2K38 and DB2, Is Your Application Safe?..."

Comment on this blog entry
Topic(s):   DB2 Application Development


SQL Injection and Stored Procedures

In doing numerous reviews of stored procedures, we see many customers with SQL injection issues. SQL injection is one of the most basic and common security issues in database applications.

Continue reading "SQL Injection and Stored Procedures..."

Comments(1)
Topic(s):   DB2 Application Development : Security


DB2 9 DBA Certification Study Questions: DB2 configuration and stored procedure calling privileges

Here are more questions from the book DB2 9 for Linux, UNIX, and Windows Database Administration: Certification Study Guide, published by MC Press, to help you prepare for the certification test. The questions are reprinted with permission. Check back each week for more, and try past questions here.

QUESTION 11
If the following SQL statement is executed:

UPDATE DB CFG FOR sample USING BLK_LOG_DSK_FUL YES

What will happen?

A. The SAMPLE database will be configured to use infinite logging.
B. The SAMPLE database will not automatically allocate additional storage space when the active log directory becomes full.
C. Log files for the SAMPLE database will be backed up automatically whenever a full backup image of the SAMPLE database is made.
D. Transactions running against the SAMPLE database will not be rolled back if they receive a disk full error.

QUESTION 12
User USER1 has the privileges needed to invoke a stored procedure named GEN_RESUME. User USER2 needs to be able to call the procedure--USER1 and all members of the group PUBLIC should no longer be allowed to call the procedure. Which of the following statement(s) can be used to accomplish this?

A. GRANT EXECUTE ON ROUTINE gen_resume TO user2 EXCLUDE user1, PUBLIC
B. GRANT EXECUTE ON PROCEDURE gen_resume TO user2;
REVOKE EXECUTE ON PROCEDURE gen_resume FROM user1, PUBLIC;
C. GRANT CALL ON ROUTINE gen_resume TO user2 EXCLUDE user1, PUBLIC
D. GRANT CALL ON PROCEDURE gen_resume TO user2;
REVOKE CALL ON PROCEDURE gen_resume FROM user1, PUBLIC;

Continue reading "DB2 9 DBA Certification Study Questions: DB2 configuration and stored procedure calling privileges..."

Comment on this blog entry
Topic(s):   DB2 Certification


Windows Vista: Where is db2diag.log location?

So, we want to post a sample for this blog from the db2diag.log. That's great; it's nice to have samples. The problem is finding the location if you took the default.

First, you must set view hidden files and folder to on. From my C: drive in Windows Explorer, I pressed the Alt key to see the menu bar. On the Tools Menu, choose Folder Options, select the view tab and choose the radio button halfway down "show hidden files and folders".

Then, you'll be able to see diag information in :

C:\ProgramData\IBM\DB2\DB2COPY1\DB2

Martin Hubel and Rob Williams
www.mhubel.com

Comment on this blog entry
Topic(s):   DB2 Application Development


DB2_CAPTURE_LOCKTIMEOUT

In the past when a lock time occurred, there was no easy way to diagnose it. Now, in DB2 9.5 and DB2 9.1 FP4 there is a new registry variable called DB2_CAPTURE_LOCKTIMEOUT to capture detailed lock timeout information. Read on to find out how to use it:

Continue reading " DB2_CAPTURE_LOCKTIMEOUT..."

Comments(1)
Topic(s):   DB2 Application Development


DB2 9 DBA Exam Sample Questions: The dbls command and the Design Advisor

If DB2 9 DBA certificiation is on your list of resolutions this year, spend some time looking over some sample questions to help you prepare. Here are more questions from the book DB2 9 for Linux, UNIX, and Windows Database Administration: Certification Study Guide, published by MC Press. The questions are reprinted with permission. Check back each week for more, and try past questions here.

QUESTION 9
Which of the following best describes the function of the db2ls command?

A. It retrieves information about the DB2 products that have been installed on a particular server.
B. It locks a DB2 system and limits access to users who hold System Administrator authority.
C. It generates a list of all remote servers that have been cataloged on a client workstation.
D. It returns the location of the system catalog for a particular database.

QUESTION 10
The Design Advisor can be used to analyze SQL from which two of the following?

A. The EXPLAIN_STATEMENT table
B. A file containing a workload
C. The SYSIBM.SYSPLANS system catalog table
D. The EXPLAIN_STREAM table
E. The ADVISE_WORKLOAD table

Continue reading "DB2 9 DBA Exam Sample Questions: The dbls command and the Design Advisor..."

Comments(1)
Topic(s):   DB2 Certification


DB2 LUW Performance: Sorts - The silent performance killer

In one of the earlier blog posts "DB2 LUW Performance: Key Cost Measures", we introduced the number of sorts per transaction (SRTTX). In a more recent post "DB2 LUW Performance: The Most Important Cost", we looked at the importance of measuring Bufferpool Logical Reads per Transaction (BPLRTX). If performing excessive and unnecessary logical I/O is the number one performance killer for a database (and it usually is), then performing excessive and unnecessary sorts is the number two performance killer in most databases.

Continue reading "DB2 LUW Performance: Sorts - The silent performance killer..."

Comment on this blog entry
Topic(s):   DB2 for LUW performance


Most read 2007 articles: DB2 and Informix

As we finish out one year and ramp up for another, I took a peek through the Web stats to see what was on visitors' minds when they stopped by the DB2mag site. I found a few surprises. Some of the most-read articles are from years ago. Two focus on Informix (evidence of that our expanded coverage there is finding an audience). Check out the top ten.

Continue reading "Most read 2007 articles: DB2 and Informix..."

Comment on this blog entry
Topic(s):   DB2 (all platforms) : Information Management in Context : Informix


DB2 9 DBA Exam Sample Questions: Terminating applications and adding storage containers

Here's a quick way to stay on track with your preparation for the DB2 9 DBA certification exam, even at this busy time of year: Try these sample questions from the book DB2 9 for Linux, UNIX, and Windows Database Administration: Certification Study Guide, published by MC Press. The questions are reprinted here with permission. Check back each week for more, and try past questions here.

QUESTION 7
A LIST APPLICATIONS command returned the following output:

A. FORCE APPLICATION (146, 147, 148)
B. FORCE APPLICATION (LOCAL.DB2.070601164915,
LOCAL.DB2.070601164914, LOCAL.DB2.070601164913)
C. FORCE APPLICATION (db2taskd, db2stmm, db2bp.exe)
D. FORCE ALLPICATION ALL
E. FORCE ALL APPLICATIONS

QUESTION 8
If the following CREATE DATABASE command is executed:

CREATE DATABASE payroll ON C:

Which of the following commands will add new storage containers to the PAYROLL database?

A. ALTER TABLESPACE ADD STORAGE D:\data1, D:\data2
B. ALTER DATABASE ADD STORAGE D:\data1, D:\data2
C. ALTER TABLESPACE ADD CONTAINERS D:\data1, D:\data2
D. ALTER DATABASE ADD CONTAINERS D:\data1, D:\data2

Continue reading "DB2 9 DBA Exam Sample Questions: Terminating applications and adding storage containers..."

Comments(3)
Topic(s):   DB2 Certification


DB2 9 DBA Exam Sample Questions: Recovering a database and LBAC

How ready are you for the DB2 9 DBA certification exam? Find out by testing your knowledge against these sample questions from the book DB2 9 for Linux, UNIX, and Windows Database Administration: Certification Study Guide, published by MC Press. The questions are reprinted here with permission. Check back each week for more, and try past questions here.

Continue reading "DB2 9 DBA Exam Sample Questions: Recovering a database and LBAC..."

Comment on this blog entry
Topic(s):   DB2 Certification


DB2 LUW: Data Security Considerations for Auditors, Security Professionals, and IT Managers

A lot of attention is being paid these days to database security and auditing. IBM has made great improvements in security and auditing with DB2 9.5 (Viper 2). Still, there remains a security vulnerability that many auditors and security administrators are not aware of, and I doubt that DBAs will rush to inform their management. Specifically, certain monitoring commands easily expose sensitive data to the DBA.

Continue reading "DB2 LUW: Data Security Considerations for Auditors, Security Professionals, and IT Managers..."

Comments(7)
Topic(s):   Security



Go on to the weblog archives...