May 2006 Archives

Many IT organizations I have seen have groups of specialists, typical are UNIX Group, DBA group etc.

When a project starts the developers on the project have to meet with all the Groups (I have more experience with the DBA group, so I write with the DBA group in mind) that they need to interact with and explain to the groups their design, the projects operational needs and other requirements, later when development starts they have to email these groups about all the setup that needs to be done and also the day to day changes that are needed. This way of working slows down the productivity of the team and the organization.

What I have found works best is when one of the member (this member could/should be rotated) of the DBA group is placed on the development team. When that happens, the team has a expert on hand they can ask him all kinds of questions, run different scenarios, design options by him, the member of the DBA group will also understand in depth what is happening on the team, what are their needs and how to tackle them, it also makes use of his link back to the group (since he is part of the DBA group, the DBA group is more likely to not ask any questions of the requirements/issues raised by him), since he knows all the members of his group, he can communicate better with the DBA group and make the tasks easier for the development team and also make it easier on the DBA group to execute the tasks expected of them. DBA's will also learn a lot from this, since now they are not sitting behind the email wall and guessing about the needs of the teams.

What does it mean to test your Database? usually when someone mentions database testing, what is that they want to test. The application code that interacts with the database, or the sql code the resides in the database like stored procedures and triggers etc. I see all these aspects to database testing as important.

Testing the applications persistence mechanism We should test that the application persists what its supposed to save and retirive the data using SQL and see if the database contains the same information that is being saved, this kind of testing makes sense when the application has complex persistence layer. This type of testing can be achived using unit tests, functional tests etc.

Testing the Database Code The database code like Views, Stored procedures etc, contains business logic that you want tested, since a lot of your application code will depend on it since database code is the API that the database provides for the application to use. You can use your application unit tests(or other test frameworks that your are using) to test the database API. You can achive this using unit tests that actually hit the database, these tests will be slow, but having some tests is better than nothing. You can also use unit testing frameworks for the database code like Ounit, PL/SQL Unit etc (more about how to use these in later posts).

In development mode you don't want to worry about which table goes into what Tablespace in production as it complicates development environments. The production DBA's want to have their input and control over deciding what table goes into what Tablespace. To allow for this I used a mapping scheme as shown below.

Lets assume we have 3 tables in our system Customer, CustomerOrder, OrderStatus. Where we are expecting Customer table to have large numbers of rows and CustomerOrder to have significanly large number of rows while OrderStatus would have few rows and not change as much. In development environments all these tables and their indexes will be put under the same tablespace. In production like environments we want to put them into seperate tablespaces.

So we had 3 types of tablespaces Small, Medium and Large to hold tables of Small, Medium and Large Sizes. We also had 3 tablespaces SmallIndex, MediumIndex and LargeIndex to hold the tables Indexes. The production DBA provided a MAP of tables and tablespace names that the table needed to reside in, when we created the tables and their indexes in production like environements we created all the objects in the same tablespace and later on a pl/sql script moved all the tables into their respective tablespaces using the MAP provided by the production DBA's.

Now lets look at the code. The tablespace MAP looks as below, where we map a table to the tablespace where the table needs to reside. By naming convention we can also move all the indexes of the table. For example the indexes of OrderStatus table will be created in tablespace named as SmallIndex

Table_Name
Tablespace_Name

ORDERSTATUS
Small

CUSTOMER
Medium

CUSTOMERORDER
Large

The pl/sql code to check if all the tables are mapped, if all tables are not mapped then we get a exception.

DECLARE
non_mapped_tables number;
BEGIN
 SELECT count(*) INTO non_mapped_tables FROM user_tables 
 WHERE table_name
    NOT IN (SELECT table_name FROM tablespace_map)
    AND table_name != 'TABLESPACE_MAP';
  IF non_mapped_tables != 0 THEN
    THROW_ERROR(non_mapped_tables,' All table/s have not been mapped to TABLESPACES');
  END IF;
END;
/

Now we are ready to move the tables and their indexes to their respective tablespaces.

DECLARE
CURSOR tabs IS
  SELECT 'ALTER TABLE ' || segment_name || ' MOVE tablespace ' stmt,m.tablespace_name
  FROM user_segments u,tablespace_map m
  WHERE
    u.segment_type = 'TABLE'
    AND u.segment_name = m.table_name
    ORDER BY u.segment_name;
CURSOR idx IS
  SELECT 'ALTER INDEX '|| segment_name || ' REBUILD tablespace ' stmt,m.tablespace_name
  FROM user_segments u,user_indexes i,tablespace_map m
  WHERE
  u.segment_type = 'INDEX'
  AND u.segment_name = i.index_name
  AND i.table_name=m.table_name
  ORDER BY u.segment_name;
BEGIN
--Move all the tables
  FOR tabsrec IN tabs LOOP
    EXECUTE IMMEDIATE tabsrec.stmt||tabsrec.tablespace_name;
  END LOOP;
--Move all the indexes
  FOR idxrec IN idx LOOP
    EXECUTE IMMEDIATE idxrec.stmt||idxrec.tablespace_name;
  END LOOP;
END;
/

Deployments done this way will sheild the development team worrying about production database specifics and allows the production DBA's to fine tune the production deployment.