Get DDL the lazy way

I had a requirement to generate DDL scripts of a particular schema in an Oracle database. I figure that there’s no way I am going to spent time cutting and pasting all these function, procedure and package codes from Toad or Sql Developer. I’m sure there is a faster way of doing this, and moreover I am too lazy to do just that.

And because of being lazy, I learned about this cool function in Oracle.

DBMS_METADATA.GET_DDL (                          
   object_type  IN VARCHAR2,
   name         IN VARCHAR2,
   schema       IN VARCHAR2 DEFAULT NULL,
   version      IN VARCHAR2 DEFAULT 'COMPATIBLE',
   model        IN VARCHAR2 DEFAULT 'ORACLE',
   transform    IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB; 

And using this function I was able to script out the DDL codes.

set pagesize 0
set linesize 300
set trimspool on
spool ddl_generator.sql;

select 'set linesize 32767 long 9999999 longc 9999999 pagesize 0 trimspool on feedback off' from dual;

select
'spool '||object_name||'.'||replace(object_type,' ','_')||'.sql;'||chr(10)||'select dbms_metadata.get_ddl('||chr(39)||replace(object_type,' ','_')||chr(39)||','||chr(39)||object_name||chr(39)||','||chr(39)||'SMSII'||chr(39)||') from dual;'||chr(10)||'spool off;'
from dba_objects where owner = 'REDDIT'
and object_type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TRIGGER');

spool off;

The script will spool another script that when run will generate the DDL codes from a particular schema.

I must say, this is a kind of situation when being lazy is actually a good thing. Because of laziness, you prevent yourself from doing boring, dull and repetitive work and seek for other solution that, when applied, can accomplish the same goal but with the least amount of effort. The process of coming up with this solution usually requires you to think harder, but the time you spend on it is definitely much more interesting and learning.

CRON

If you have ever worked on a UNIX/UNIX-like OS, Im’ pretty sure how quick you’ll get to admire those amazing and powerful OS features and command lines. And if you also work on Windows and do a lot of automation, I’m sure you’ll love it that sometimes you wish you had that feature in Windows. One of my favorite is CRON, the time-based job scheduler in Unix-like computer operating systems. Yes, I know we have the good old Task Scheduler in Windows, but I really like how you can configure a complex scheduled job in one line of text with CRON.

It maybe a little over the top to create something like this on Windows, considering that Task Scheduler will be able to achieve the same goal. But I got this really strange error in one of my scheduled job that kept me head scratching for days. The job will just show in running state, it will never run and the “end task” option is greyed out so the only fix is to restart the scheduler service. I normally try to investigate the problem and find the root cause but this time I had enough that it made me decide to create the scheduler I want – one with a CRON-like syntax. And luckily, I dont have to. QUARTZ.NET, a .NET open source library already has this functionality. So I just need to build my program on top of it.

With the help of QUARTZ, I created a windows service that reads the configuration files for scheduled jobs. The time schedule uses a CRON like syntax.

Below is a section in the AppConfig where you can schedule the jobs:

 <section name="jobs"
    type="Assyst.JobProcessor.Scheduler.JobConfigurationHandler, Assyst.JobProcessor.Scheduler"/>

 <jobs>
 
  <job name="MyJob"
    type="MyProject.MyJobClass, MyProject"
    schedule="30 0/1 * * * ?" /> 
 </jobs>

JobConfigurationHandler is an implementation of IConfigurationSectionHandler interface, which reads the AppConfig for the scheduled jobs. And MyJobClass is a class which derives from an abstract class, AssystJobBase, which is simply a template class to easily create job class. This abstract class implements the IAssystJob Interface which is the only requirement for a class to be configured as scheduled job. The schedule attribute obviously is where you put your CRON-like syntax for scheduling the job.

I have temporarily uploaded the project here but it will soon be on my GITHUB repository.

Monitor UNIX Disk Space

A simple script for monitoring UNIX Disk space usage that sends an email alert if % usage is above the alert level.

THRESHOLD=90

RECIPIENTS="admin@company.com.qa"
DISK_ALERT_LOG="/tmp/diskalert.log"
DISK_ALERT_MAIL="/tmp/diskalert.mail"

touch ${DISK_ALERT_LOG}

df -h | awk '{if(NR>1){print$6" "$5}}' | while read output
do
  USAGE=`echo $output | sed 's/\%//' | awk '{print$2}'`
  MOUNT=`echo $output | awk '{print$1}'`
  if [ ${USAGE} -gt ${THRESHOLD} ];
  then
    echo "${MOUNT} = ${USAGE}%" >> ${DISK_ALERT_LOG}
  fi
done

cat ${DISK_ALERT_LOG}

if [ -s ${DISK_ALERT_LOG} ];
then
  echo "To: ${RECIPIENTS}" > ${DISK_ALERT_MAIL}
  echo "From: ${HOSTNAME}" >> ${DISK_ALERT_MAIL}
  echo "Subject: Disk space alert on ${HOSTNAME}" >> ${DISK_ALERT_MAIL}
  echo >> ${DISK_ALERT_MAIL}
  cat ${DISK_ALERT_LOG} >> ${DISK_ALERT_MAIL}
  cat ${DISK_ALERT_MAIL} | /bin/mail ${RECIPIENTS}
fi

rm ${DISK_ALERT_LOG}

DTC Error

Yesterday I was installing windows 2003 cluster on VMWare machines. Then I got this weird error message while I was configuring MS DTC (Distributed Transaction Coordinator) cluster application. The application and cluster resource was configured successfully but when I fail over, MS DTC failed to start and looking at the event logs gives me this error message:

The MS DTC log file is an incompatible version

This is really strange since the cluster nodes are exactly of the same VMWare image. A quick google shows me only few results, mostly from MS site. The first site does have the usual MS instructions – “Reinstall/Install”, which in my case doesn’t resolved the problem. The other sites are just lists of event ids and some tables which doesn’t help at all. So it seems that I have to use a bit more of my google-fu. And sure enough changing the search key words to “msdtc cluster failover error” yields more interesting results but in a quite overwhelming numbers (Maybe my google-fu is still not that strong enough). Going over through most of the sites and forums, a common advice is to delete and reset the MSDTC logs using the following instructions:

Delete the MSDTC.LOG file from %systemroot%\system32\Msdtc directory

Reset the logs:

msdtc -resetlogs

Unfortunately it still did not fix the problem. Until I checked the MSDTC properties from the “Component Services” console and noticed this:

DTC logs seems to be stored in the quorum disk if clustered. And since this is just a new installation, on a VMWare environment and will be used for testing purposes (If you are having the same issue on a production server, I strongly suggest that you contact Microsoft Premiere Support first before doing this stunt), I went on and deleted the log file stored in the quorum disk and reset the DTC log. And viola, I got my cluster working and ready for SQL 2005 Cluster installation.

The Git Effect

Many developers, including those notable persons over the .NET community have been moving their open source projects to Git and Github lately. (Git is a free and open source, distributed version control system. And Github is an online project hosting that uses Git as its revision control system.)

Rob Conery’s SubSonic is now on Github. As of September 14, 2009, Sharp Architecture’s source repository is now being hosted at http://Github.com/codai/Sharp-Architecture. Oren Eini (aka Ayende Rahien) has recently moved to Git, not only he is a famous NHibernate developer and creator of RhinoMocks, but interestingly a long time user of SubVersion and a developer of SvnBridge (SvnBridge allows you to use TortoiseSVN and other Subversion clients with Team Foundation Server).

I’ve been using SubVersion for my hobby projects and I consider it as an excellent version control system. Far better than VSS - which unfortunately my company is still using.

But is there any compelling reason to try Git or even completely switch from SubVersion (or any other version control system) and not just because it’s what the “Kool Kids” are using? TBH, I really don’t know. But there is one thing that I am sure of - the recent noise about Git is something a developer cannot ignore (at least for the curious ones). And Aaron Jensen is right, maybe I heard  it enough that I want to give it a try and see why they think it’s so great.

And sure enough I did. I have setup a GitHub account to host my hobby projects. Currently I have only my “splotter-common” project uploaded. As of now, the project contains the common code base of my own implementation (inspired by Oren’s RhinoCommons) of Data Persistence, Repository and Unit of Work pattern using NHibernate, Linq for NHibernate and Linq to SQL.