Thursday, December 23, 2010

Calculating Data and Log Space Usage in Sybase

Below is the SQL statement that calculates how much space is allocated and used by data and log segments in Sybase.


SELECT "Database Name" = CONVERT(char(20), db_name(D.dbid)),
"Data Size" = 
 STR(SUM(CASE
          WHEN U.segmap != 4 THEN U.size*@@maxpagesize/1048576 
        END
     ),10,1),
"Used Data" = 
  STR(SUM(CASE 
           WHEN U.segmap != 4 
            THEN size - 
                   curunreservedpgs(U.dbid, U.lstart, U.unreservedpgs)
           END
         )
       *@@maxpagesize/1048576,10,1),

"Data Full%" = 
  STR(100 * 
      (1 - 1.0 *
         SUM(CASE
              WHEN U.segmap != 4 
                THEN curunreservedpgs(U.dbid, U.lstart, U.unreservedpgs) 
              END
            ) / SUM(CASE
                      WHEN U.segmap != 4 THEN U.size 
                    END
                   )
       )
     ,9,1) + "%",

"Log Size" =
  STR(SUM(CASE
            WHEN U.segmap in (4, 7) THEN U.size*@@maxpagesize/1048576
  END),10,1),

"Free Log" = 
  STR(lct_admin("logsegment_freepages",D.dbid)*@@maxpagesize/1048576,10,1),

"Log Full%" = 
 STR(100 * (1 - 1.0 * lct_admin("logsegment_freepages",D.dbid) 
     / SUM(CASE WHEN U.segmap in (4, 7) THEN U.size END)),8,1) + "%"
FROM master..sysdatabases D,
     master..sysusages    U
WHERE U.dbid = D.dbid 
AND (((D.dbid > 3) AND (D.dbid < 31513) OR D.dbid = 2)  AND (D.status != 256))
GROUP BY D.dbid
ORDER BY db_name(D.dbid)

Some Notes

1.- This part of WHERE clause:
(((D.dbid > 3) AND (D.dbid < 31513) OR D.dbid = 2)
means "all user databases ("dbid from 4 to 31512") and tempdb ("dbid=2").

2.- @@maxpagesize - pagesize, the server level parameter - can be 4K (4096), standard in Sybase 15.5, but can be changed when the server is being configured, or 2K (2048) standard for older versions.

3.- U.segmap in (4, 7) - covers both cases of log pages location: 4 - separate log segment, 7 - shared by log and datapages.
Allocation and usage numbers reported in the latter case will be the same for data and log (as the same space is shared by both).

Thursday, December 16, 2010

Sample Spring Publishing/Subscribing Application for ActiveMQ

Here is an example of an application that uses Spring to connect to ActiveMQ messaging service. This code is very similar to the one described in the post about a simple publisher for MQSeries.
The software below allows you to send a text message (sent in the main method of SpringJMSSendTest: sender.send("Test Message 12345");). Once the message is sent, the same code will receive it.
The listener reconnects on exception - i.e. if ActiveMQ is stopped, or has not been started, this program will be trying to reconnect until it connects (this behavior is defined by a property of CachingConnectionFactory <property name="reconnectOnException" value="true" />.


We can start with XML config (jms-config.xml)

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://www.springframework.org/schema/beans 
 http://www.springframework.org/schema/beans/spring-beans-2.0.xsd">

 <!-- a pool based JMS provider -->
 <bean id="jmsFactory" 
          class="org.apache.activemq.pool.PooledConnectionFactory"
          destroy-method="stop">
  <property name="connectionFactory">
   <bean class="org.apache.activemq.ActiveMQConnectionFactory"> 
    <property name="brokerURL">   
      <value>tcp://localhost:61616</value>
    </property>
   </bean>
  </property>
 </bean>
 
 <bean id="MQcachedConnectionFactory"
          class="org.springframework.jms.connection.CachingConnectionFactory">
    <property name="targetConnectionFactory" ref="jmsFactory" />
    <property name="reconnectOnException" value="true" />
    <property name="exceptionListener" ref="exceptionListener" />
    <property name="sessionCacheSize" value="1" />
 </bean>
 
 <bean id="exceptionListener" class="com.amq.test.ExceptionListenerTest">
    <property name="cachingConnectionFactory" 
        ref="MQcachedConnectionFactory"   
    /> 
 </bean>
 
 <!-- Spring JMS Template -->
 <bean id="myJmsTemplate" class="org.springframework.jms.core.JmsTemplate">
   <property name="connectionFactory">
     <ref local="MQcachedConnectionFactory" />
   </property>
   <property name="defaultDestination" ref="defaultDestination" />
 </bean>

 <!-- ActiveMQ destination to use by default -->
 <bean id="defaultDestination" 
          class="org.apache.activemq.command.ActiveMQQueue">
   <constructor-arg value="test_queue" />
 </bean>

 <bean id="springJMSListenTest" class="com.amq.test.SpringJMSListenTest">
 </bean>

 <bean id="messageListenerTest" class="com.amq.test.MessageListenerTest" />

 <bean id="listenerContainer"
   class="org.springframework.jms.listener.DefaultMessageListenerContainer">
  <property name="connectionFactory" ref="MQcachedConnectionFactory" />
  <property name="destination" ref="defaultDestination" />
  <property name="messageListener" ref="messageListenerTest" />
  <property name="concurrentConsumers" value="6" />
  <property name="acceptMessagesWhileStopping" value="false" />
  <property name="recoveryInterval" value="10000" />
  <property name="cacheLevelName" value="CACHE_CONSUMER" /> 
 </bean>

 <!--  Publisher -->
 <bean id="jmsSendTest" class="com.amq.test.SpringJMSSendTest">
    <property name="jmsTemplate" ref="myJmsTemplate"/>
 </bean>

</beans>


********************************* Now the Java part ************************

ExceptionListener - you can live without it, but if you want to be able to process exceptions, it is a good idea to create one. ExceptionListener is referenced in XML in:
<bean id="exceptionListener" class="com.amq.test.ExceptionListenerTest">


package com.amq.test;

import javax.jms.ExceptionListener;
import javax.jms.JMSException;

import org.springframework.jms.connection.CachingConnectionFactory;

                                                                           
// import com.sssw.jms.api.JMQConnectionLostException;

public class ExceptionListenerTest implements ExceptionListener
{
 CachingConnectionFactory cachingConnectionFactory;
 
 public void onException(JMSException arg0) {
  System.err.println("Exception occurred "+arg0);
  cachingConnectionFactory.onException(arg0);
 }

 public CachingConnectionFactory getCachingConnectionFactory() {
  return cachingConnectionFactory;
 }

 public void setCachingConnectionFactory(
                        CachingConnectionFactory  cachingConnectionFactory) {
  this.cachingConnectionFactory = cachingConnectionFactory;
 }
}



MessageListener - This is the place where the messages we receive are being processed.


package com.amq.test;

import javax.jms.JMSException;
import javax.jms.Message;
import javax.jms.MessageListener;
import javax.jms.TextMessage;

public class MessageListenerTest implements MessageListener {

  public void onMessage(Message message) {

    if (message instanceof TextMessage) {

      try {
         System.out.println("Received Message:["+
                      ((TextMessage) message).getText()+"]");
      } 
      catch (Exception ex) {    
         System.out.println("Exception in onMessage " + ex.toString() + "\n" +
                     ex.getStackTrace());
      }
    } 
    else {
      System.out.println("Message must be of type TextMessage");
    }
  }
}



SpringJMSListen - use it if you do not want to publish anything, just to listen


package com.amq.test;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.FileSystemXmlApplicationContext;


public class SpringJMSListenTest {

  public SpringJMSListenTest() {
  }

  public static void main(String[] args) {
      try {
            System.out.println("trying to get spring context");
            ApplicationContext ctx =  
                 new FileSystemXmlApplicationContext("config/jms-config.xml");  

            System.out.println("got spring context");

            SpringJMSListenTest springJMSTest =   
                (SpringJMSListenTest)ctx.getBean("springJMSListenTest");
         } 
         catch (Exception ex) {
            ex.printStackTrace();
         }
  }
}


SpringJMSSend - use it to start the software if you need to send a test message to the queue and receive it.


package com.amq.test;

import javax.jms.JMSException;
import javax.jms.Message;
import javax.jms.Session;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.FileSystemXmlApplicationContext;
import org.springframework.jms.core.JmsTemplate;
import org.springframework.jms.core.MessageCreator;

public class SpringJMSSendTest {
 
 private JmsTemplate jmsTemplate;

 public JmsTemplate getJmsTemplate() {
     return jmsTemplate;
 }

 public void setJmsTemplate(JmsTemplate MQjmsTemplate) {
     this.jmsTemplate = MQjmsTemplate;
 }

 public void send(final String txt){

     if(jmsTemplate==null){
        System.out.println("Template is null!!");
     }

     System.out.println("Sending Message:["+txt+"]");
     jmsTemplate.send( 
           new MessageCreator() {
                public Message createMessage(Session session) 
                throws JMSException {
                     return session.createTextMessage(txt);
         }
           }
     );
  
     System.out.println("Message Sent");
 }

 public static void main(String argc[]){
     try {
           System.out.println("trying to get spring context");
           ApplicationContext ctx =  
             new FileSystemXmlApplicationContext("config/jms-config.xml");  
           System.out.println("got spring context");

           SpringJMSSendTest sender = (SpringJMSSendTest)ctx.getBean("jmsSendTest");
            
           sender.send("Test Message 12345");
     } 
     catch (Exception ex) {
           System.out.println("Exception: "+ex.toString());
           ex.printStackTrace();
     }

 }
}

NOTES
- The configuration XML assumes that ActiveMQ server is running locally.
- Publisher will not try to reconnect automatically, only listeners do that
- The code above was tested in Eclpse with Java 1.6

Friday, December 10, 2010

Tricks and Tips of UNIX Shell Programming

1. Getting timestamp of a file creation:



#!/bin/sh

FILE_TO_CHECK="/logs/mylog.log"
FILE_DATE=`ls -lrt ${FILE_TO_CHECK} | tr -s " " | cut -d" " -f6-8`
CONVERTED_FILE_DATE=$(date -d "${FILE_DATE}" '+%Y%m%d')

echo ${CONVERTED_FILE_DATE}
Returns date in format: 20101209

2. Comparing timestamps in format HHMM (like: 1201 or 0901, etc.) with NOW



We can get time (HHMM) part of the date and compare it to another timestamp as integer values. We just need to make sure that leading 0s are processed properly (for timestamps like 0000-0959).
This can be achieved by the following code: echo 0020 | sed 's/^0*//'.
Returned result will be: 20

For those, not familiar with regular expressions, the expression provided for sed means - replace one or more 0s at the beginning of the line with empty string - i.e. delete leading 0s.

#!/bin/sh

NEEDED_TIME_HHMM=0001

CURR_DATETIME=`date '+%Y%m%d%H%M%S'`
CURR_TIME_HHMM=`date '+%H%M'`
CURR_DATE=`date '+%Y%m%d'`

if [[ $(echo ${NEEDED_TIME_HHMM} | sed 's/^0*//') -le \
       $(echo ${CURR_TIME_HHMM} | sed 's/^0*//') ]]
then
  echo “${NEEDED_TIME_HHMM} le ${CURR_TIME_HHMM}”
else
  echo “NOT ${NEEDED_TIME_HHMM} le ${CURR_TIME_HHMM}”
fi

echo “Compared  $(echo ${NEEDED_TIME_HHMM} | sed 's/^0*//') \ 
 and $(echo ${CURR_TIME_HHMM} | sed 's/^0*//')”

3. Starting a script in the background with output going to a log file



A script can be started in the background as follows:

./run_mycode.sh $1 > /var/tmp/logs/run_mycode.log 2>&1 &

where
- "$1" is a parameter (can be more: $2, $3, etc.)
- "> /var/tmp/logs/run_mycode.log" redirects output into the log file
- "2>&1" redirects STDERR to STDOUT, so that error messages will be visible in the same log with other messages. This is especially important when you start SYBASE isql in your script, as print command sends output to STDERR while select sends output to STDOUT. Further explanations regarding redirection in UNIX can be found here.
- final "&" means "run in the background"

4. To clean all the files with name like *.log older than 7 days, run:


VCS_LOG_DIR=/mydirectory
    find ${VCS_LOG_DIR} -name "*.log" -mtime +7 -exec rm -f {} \;
if you want to delete all the files up-to 7 days old, replace +7 for -7

5. Working with a list of values in a shell script


Here is a simple example that illustrates how to use a list of values saved in a file.
First create a file config_list.txt and populate it with values as follows

myproc_a
myproc_b
myproc_c
myproc_d

Now create the script below and execute it


#!/bin/sh

SCRIPT_DIR=.
CONFIG_DIR=.

SERVERS=`cat ${CONFIG_DIR}/config_list.txt`

for SERVER in $SERVERS
do
  count=`expr $count + 1` 
  echo "Monitoring Server ${count}  Named ${SERVER}..." 
done

The output will look like

Monitoring Server 1 Named myproc_a...
Monitoring Server 2 Named myproc_b...
Monitoring Server 3 Named myproc_c...
Monitoring Server 4 Named myproc_d...

NOTE: Variable $count in your script counts the items in your list.

6. Forcing symbolic link

If you need to force an existing symbolic link to a different directory you can do it as follows

ln -s myOldDir myLink
$ ls -lrt
drwxr-xr-x 9 user1 users     4096 May  9 18:24 myOldDir
drwxr-xr-x 9 user1 users     4096 May  9 18:24 myNewDir
lrwxr-xr-x 9 user1 users       22 May  9 18:24 myLink -> myOldDir


Now force the relinking
ln -sfn myNewDir myLink
$ ls -lrt
drwxr-xr-x 9 user1 users     4096 May  9 18:24 myOldDir
drwxr-xr-x 9 user1 users     4096 May  9 18:24 myNewDir
lrwxr-xr-x 9 user1 users       22 May  9 18:24 myLink -> myNewDir

7. Using awk to calculate a sum of values in a file


Let's say we have a file with the data like below

testdata 1 testdata000000000000001.11 sfd
testdata 1 testdata000000000000002.22 sfd
testdata 1 testdata000000000000003.33 sfd
testdata 1 testdata000000000000001.01-sfd
testdata 1 testdata000000000000001.01-sfd
A numeric value is written in positions 20-37, a sign is in position 38, space means positive number
The command below will calculate the sum of the numbers. I had to split it into 2 lines to fit into this area, hence the \ in the end of the first line

awk '{ s=substr($0,38,1); $n=substr($0,20,18)+0; var=sprintf("%.2f", s $n); \
sum=sum+var;} END { printf("%.2f", sum);}' testdata.txt


Returns 4.64


8. Dynamic Variables (Variable Variable) in bash and ksh


Below are two scripts that demonstrate usage of dynamic variables in bash and ksh.
#!/bin/bash

VAR_AAA=text_for_aaa
VAR_BBB=text_for_bbb

TYPE=AAA
VAR_DYNO=VAR_${TYPE}

echo ${VAR_DYNO}=${!VAR_DYNO}
#!/bin/ksh

VAR_AAA=text_for_aaa
VAR_BBB=text_for_bbb

TYPE=AAA
typeset -n VAR_DYNO="VAR_${TYPE}"

echo VAR_${TYPE}=${VAR_DYNO}

Both scripts return VAR_AAA=text_for_aaa

9. Using an array to verify connectivity to multiple hosts

If you need to check if you have connectivity to a list of hosts you can use the following script


#!/bin/sh 
 
funtln () {
telnet ${SRV} << EOO
 quit
EOO

}

SERVERS=( '192.168.1.105:443' '192.168.1.105:80' '192.168.1.105:443')

echo ${SERVERS[*]}

for SERVER in ${SERVERS[@]}
do
  count=`expr $count + 1`
  echo "Testing Server ${count}  Named ${SERVER}..."
  SRV=`echo ${SERVER} | sed 's/:/ /'`
  funtln
done


10. Using Dynamic Array Variables in bash

If you need to check a log file for patterns used by some applications. Each of the applications can leave different patterns in a log file. You can use dynamic array variables in bash for this. Here is how.

Create a config file /tmp/test.conf with the following contents

APPS=("APP1" "APP2" "APP3")
APP1=("A1_123" "A1_456")
APP2=("A2_987")
APP3=("A3_123" "A3_456")

APPS is a list of applications, APP1, APP2, APP3 are the lists of patterns that we want to check for each application.

The code will look like following

#!/bin/bash

. /tmp/test.conf

for APP in ${APPS[@]}
do
  VAR_APP=${APP}
  echo "----Checking for ${VAR_APP}"

  eval TAGS=( '"${'${APP}'[@]}"' )
  for TAG in "${TAGS[@]}"
  do
    echo "TAG=${TAG}   "
    #Put  grep ${TAG} mylogfile | wc -l  here to get the counts
  done
done

We source the config file, then go through the APPS array to get the names of the arrays that contain the patterns.

If you run this program you will get the following output

----Checking for APP1
TAG=A1_123
TAG=A1_456
----Checking for APP2
TAG=A2_987
----Checking for APP3
TAG=A3_123
TAG=A3_456

Monday, December 6, 2010

Deleting Durable Subscription in EMS

Sometimes we need to delete a durable subscription that we created on a topic while testing some software component. Once the test is done, we do not want durable to exist on the server (if there is no consumer running for this durable subscription). The same test topic can be reused for some other tests and messages will pile up in our unused durable.
To delete durable subscription we need to use unsubscribe method of the session. Here is simple utility that can be used to achieve just that (written for Tibco EMS).

First - let's create a class that will hold connection parameters. We will try to make this class generic enough, to be useful for other utilities that use MQ or EMS.


/***********************************************************
** UtilJMSConnectionParameters
************************************************************/
package utilities;

public class UtilJMSConnectionParameters {

 private String Host;
 private String Port;
 private String Queue;
 private String Topic;
 private String Durable;
 private String Channel;
 private String Queuemanager;
 private String User;
 private String Password;
 
 private String Commit;

 private String Filename;
  
 /*
  * Getters, Setters
  */
 
 public String getFilename() {
  return Filename;
 }
 public void setFilename(String filename) {
  Filename = filename;
 }

        /*
        ** ..... Add more Getters/Setters
        */

 /**
  * isEmptyUser
  */
 public boolean isEmptyUser(){
  return isEmptyField(this.User);
 }

 /**
  * isEmptyPassword
  */
 public boolean isEmptyPassword(){ 
  return isEmptyField(this.Password);
 }
 
 public boolean isEmptyHost(){ 
  return isEmptyField(this.Host);
 }

 public boolean isEmptyDurable(){ 
  return isEmptyField(this.Durable);
 }
 
 public boolean isEmptyQueue(){ 
  return isEmptyField(this.Queue);
 }

 public boolean isEmptyTopic(){ 
  return isEmptyField(this.Topic);
 }

 public boolean toCommit(){ 
  boolean retEmpty = true;

  if(this.Commit == null){
   retEmpty = false;
  }
  
  return retEmpty;
 }

 /**
  * 
  * @param field
  * @return
  */
 private boolean isEmptyField(String field){
  boolean retEmpty = false;
  if(field == null){
   retEmpty = true;
  }
  else if (field.trim().length() == 0){
   retEmpty = true;
  }
  
  return retEmpty;
  
 }
 
    /**
     * toString
     */
    public String toString(){
     
     StringBuffer str = new StringBuffer();
     
     str.append(" host:         "+this.Host);
     str.append("\n port:         "+this.Port);
     str.append("\n channel:      "+this.Channel);
     str.append("\n queuemanager: "+this.Queuemanager);
     str.append("\n topic:        "+this.Topic);
     str.append("\n durable:      "+this.Durable);
     str.append("\n queue:        "+this.Queue);
     str.append("\n user:         "+this.User);
     str.append("\n password:     "+this.Password);
     if(this.toCommit()){
        str.append("\n Commit requested");
     }
    
     return str.toString();
    }
}
/************************************************
** End of UtilJMSConnectionParameters definition
*************************************************/

Now - the main class

We are using CmdLineParameterReader class described in the previous post to get parameters from command line.


/**********************************************************
** UtilJMSTibUnsubscribe
***********************************************************/
package utilities;

import javax.jms.Connection;
import javax.jms.ConnectionFactory;
import javax.jms.ExceptionListener;
import javax.jms.JMSException;
import javax.jms.Session;

public class UtilJMSTibUnsubscribe implements ExceptionListener {

       private static UtilJMSConnectionParameters connParams = 
                          new UtilJMSConnectionParameters();
       
       /**
        * UtilJMSMQListen
        * 
        * @param argc
        */
        private UtilJMSTibUnsubscribe(String[] argc){
              
              CmdLineParameterReader cmdReader = new CmdLineParameterReader("-");
              
              connParams.setHost(cmdReader.getParameterValue(argc,"-host"));
              connParams.setPort(cmdReader.getParameterValue(argc,"-port"));
              connParams.setDurable(cmdReader.getParameterValue(argc,"-durable"));
              connParams.setUser(cmdReader.getParameterValue(argc,"-user"));
              connParams.setPassword(cmdReader.getParameterValue(argc,"-pass"));
              
              if(connParams.isEmptyHost() || connParams.isEmptyDurable()){
                   System.out.println("Provide parameters: -host [hostname] "+
                                       "-port [port] -durable [durable] " +
                                       "-user [user] -pass [password]\n " +
                                    "  need at least host and durable name \n");
               
                    System.exit(1);
              }
         }
       
         /**
          * MQListen
          * 
          * @param connParams
          */
       private void TibListen(UtilJMSConnectionParameters connParams){
              
            System.out.println("Connection arameters\n"+
                     connParams.toString()+"\n");
              
            Connection connection = null;
            Session session = null;
        
            try{
                  /* Create a connection factory */
                  ConnectionFactory factory = 
                    new com.tibco.tibjms.TibjmsConnectionFactory(
                     "tcp://"+connParams.getHost()+":"+connParams.getPort()
                    );
                  
                  /* Create a connection */
                  connection = factory.createConnection(connParams.getUser(),
                          connParams.getPassword());

                  /* Create Sessioin */
                  session = connection.createSession(false, 
                          Session.AUTO_ACKNOWLEDGE);

                  /* set the exception listener */
                  connection.setExceptionListener(this);

                  
                  /* unsubscribe */
                  session.unsubscribe(connParams.getDurable());
                  System.out.println("Unsubscribed "+ connParams.getDurable());
                  connection.close();
                  System.out.println("Connection closed, exiting ");
                  System.exit(0);

           }
           catch(Exception ex){
                  System.out.println("Exception: "+ex.toString());
                  ex.printStackTrace();
           }
       }
       
       /**
        * onException
        */
       public void onException(JMSException ex) {
           System.err.println("Connection exception received");
           System.out.println("Connection exception "+ex.toString());
           ex.printStackTrace();
           System.exit(-1);
       }

       /**
        * 
        * @param argc
        */
       public static void main(String[] argc){
              UtilJMSTibUnsubscribe listener = new UtilJMSTibUnsubscribe(argc);
              listener.TibListen(connParams);
       }

}
/**************************************************
** End of UtilJMSTibUnsubscribe definition
***************************************************/

Compile this utility and jar it into UtilJms.jar

This utility can be used from a shell script that will look something like below.


#!/bin/bash
JAVA_HOME=/bd/apps/java/jrrt-3.1.0-1.5.0
export JAVA_HOME

if [ $# -lt 4 ]; then 
 echo "Usage:JMSUnsubscribe.sh [host] [port] [durable] [user] [pass]"
 exit ;
fi

HOST=$1
PORT=$2
DURABLE=$3
USER=$4
PASSWORD=$5

ClassPath="";
ClassPath=${ClassPath}"./lib/UtilJms.jar:"
ClassPath=${ClassPath}"./lib/jms.jar:"
ClassPath=${ClassPath}"./lib/tibjms.jar:"

export ClassPath
echo "ClassPath:${ClassPath}"

$JAVA_HOME/bin/java  -classpath "${ClassPath}" utilities.UtilJMSTibUnsubscribe \
 -host ${HOST} -port ${PORT} -durable ${DURABLE} -user ${USER} -pass ${PASSWORD}

NOTE:
Be careful - if you have any messages piled up, unsubscribing will cause the server to drop them.

Friday, December 3, 2010

JMS MQ Series Publisher using Spring

Here is a simple program that allows to connect to MQ Series server, read a text file with messages (one message per line) and publish these messages to MQ.

This publisher gets allthe connection parameters from an XML file mq-service-FL.xml
that is looked up in the class path.

The name of the file with data is passed as a parameter in the command line.
If a message in the data file is shorter than 100 bytes, the message is skipped.

Code in Java is below
/**************************************************************
** Class MQAsyncSender
***************************************************************/
package com.mq.sendmsg;

import javax.jms.JMSException;
import javax.jms.Message;
import javax.jms.Session;


import org.springframework.jms.core.JmsTemplate;
import org.springframework.jms.core.MessageCreator;

public class MQAsyncSender {

  private JmsTemplate jmsTemplate;

  /**
   * @return
   */
   public JmsTemplate getJmsTemplate() {
       return jmsTemplate;
   }

  /**
   * 
   * @param MQjmsTemplate
   */
   public void setJmsTemplate(JmsTemplate MQjmsTemplate) {
     this.jmsTemplate = MQjmsTemplate;
   }

  /**
   * 
   * @param txt
   */
   public void send(final String txt){
     if(jmsTemplate==null){
        System.out.println("Template is null!!");
     }
     jmsTemplate.send(new MessageCreator() {
          public Message createMessage(Session session) throws JMSException 
          {
            return session.createTextMessage(txt);
          }
     });
     System.out.println("Message Sent:["+txt+"]");
   }
}
/*
* End of MQAsyncSender class definition
*/

/*********************************************************************
** Main class
**********************************************************************/
package com.mq.sendmsg;

import java.io.BufferedReader;
import java.io.FileReader;
import java.util.Arrays;

import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jms.connection.*;

import com.ibm.mq.jms.MQQueueConnectionFactory;
import com.ibm.msg.client.jms.*;

public class MQSimpleFileReaderSend {

  public static void main(String args[]){
    String         line           = null;
    StringBuffer   msgTextBuffer  = null;
    BufferedReader in             = null;
    String         msgText        = null;
    String         dataFileName = null;
    String         actionCode     = "A";
    int            msgcount       = 0;
    int            totalMsgs      = -1;
    int            skipMsgs       = 0;
    String         headerRecord   = "";

    try{
      if(args.length <1 ){
         throw new Exception("Incorrect arguments.Need atleast 2" +
            "arguments");        
      }
      dataFileName = args[0];    
   }
   catch(Exception ex){
       ex.printStackTrace();
       usage();
   }

   System.out.println("Starting for config mq-service-FL.xml");    
   ClassPathXmlApplicationContext ctx = 
        new ClassPathXmlApplicationContext("mq-service-FL.xml"); 
   MQAsyncSender sender=(MQAsyncSender)ctx.getBean("jmssend");     
   System.out.println("sender: Messages will be sent to \n"+               
      getSenderConnectionParameters(sender));

   try{
     in = new  BufferedReader(new FileReader(dataFileName));
     while (( line= in.readLine()) != null) {
         msgcount++;
         msgTextBuffer=new StringBuffer(line);          

         if(msgTextBuffer.length()<100) {
            System.out.println("Message "+msgcount + 
                   " less than 100 chars...Hence skipping");            
            skipMsgs++;
            continue;
         }          
         msgText=addHeaderForActionH(msgTextBuffer,actionCode,headerRecord);        
         sender.send(msgText);
 
         if(msgcount==totalMsgs){
            break;
         }
    }
    System.out.println("Number of msgs sent:"+ (msgcount-skipMsgs));     
    
    if(skipMsgs>0){
      System.out.println("Number of Messages skipped:"+skipMsgs);
    }
    in.close();
   }  
   catch(Exception e){
    e.printStackTrace();
   }
  }

 /**
  * 
  */
  private static void usage()
  {
    System.err.println("\nUsage: startpublisher.sh  ");    
    System.exit(0);  
  }
    
 /**
  *
  */
  private static String getSenderConnectionParameters(
     MQAsyncSender sender){

      String strOut="";
      strOut=strOut + "baseQueueName:"+        
        sender.getJmsTemplate().getDefaultDestination().toString()+"\n";
      return strOut;
  }    
 /**
  * addHeaderForActionH (Modify if you need 
  * to add standard header to all records)
  * @param msgText
  * @param actionCode
  * @return
  */
  public static String addHeaderForActionH(StringBuffer msgText,
                                          String actionCode, String header){      
   return msgText.toString();  
  }
}      


List of needed JARs

com.ibm.mq.jar
com.ibm.mq.jmqi.jar
com.ibm.mqjms.jar
commons-logging-1.1.1.jar
dhbcore.jar
j2ee-1.4.jar
spring-2.5.jar
spring-beans.jar
spring-context.jar
spring-core.jar
spring-jms.jar


Below is XML that allows to create JMS connection to MQ Series using Spring

<beans xmlns="http://www.springframework.org/schema/beans" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:lang="http://www.springframework.org/schema/lang"
    xsi:schemaLocation="http://www.springframework.org/schema/beans 
     http://www.springframework.org/schema/beans/spring-beans-2.0.xsd     
     http://www.springframework.org/schema/lang
     http://www.springframework.org/schema/lang/spring-lang-2.0.xsd">

  <bean id="MQjmsqueuetemplate" 
      class="org.springframework.jms.core.JmsTemplate">
       <property name="connectionFactory" 
           ref="MQcredentialsconnectionFactory" />
       <property name="defaultDestination" 
           ref="MQdestination" />

  </bean>
 
  <bean id="MQconnectionFactoryparams" 
      class="com.ibm.mq.jms.MQQueueConnectionFactory">
       <property name="transportType">
          <value>1</value>
       </property>
       <property name="queueManager">
          <value>QMGR1</value>
       </property>
       <property name="hostName">
          <value>server1.mydomain.net</value>
       </property>
       <property name="port">
            <value>1111</value>
        </property>
        <property name="channel">
            <value>BUSINESS.CHANL1</value>
        </property> 
  </bean>

  <bean id="MQdestination" class="com.ibm.mq.jms.MQQueue">
        <property name="baseQueueName">
            <value>QUEUE.BUSINESS.IN.TEST</value>
        </property>
  </bean>

  <bean id="MQcredentialsconnectionFactory" class=
"org.springframework.jms.connection.UserCredentialsConnectionFactoryAdapter">
        <property name="targetConnectionFactory" 
           ref="MQconnectionFactoryparams"/>
        <property name="username" value=""/>
        <property name="password" value=""/>   
  </bean>

  <bean id="jmssend" class="com.mq.sendmsg.MQAsyncSender">
        <property name="jmsTemplate" ref="MQjmsqueuetemplate"/>
  </bean>
   
</beans>

Sunday, November 21, 2010

Pivoting tables in SQL

Sometimes we need to pivot a table in SQL.

Imagine a table (called test1) with monthly sales numbers

yearmonthSalesPersonSales $
20096a6.1
20096b6.2
20097a7.1
20098a8.1
20098b8.2
20099b9.2
20101a1.1
20102b2.1
20103b3.1
20104a4.1
20104b4.2
20105a5.1

You can create and populate your test table using this SQL (works for Sybase and MS SQL server):
/***********************************************************/
if exists (select 1 from sysobjects where name='test1' and type='U')
   begin
     drop table test1
   end
go

create table test1
(
 year   int,
 month  int,
 sales_team char(4),
 sales  float
)

insert test1 select 2009, 6, "a", 6.1
insert test1 select 2009, 6, "b", 6.2
insert test1 select 2009, 7, "a", 7.1
insert test1 select 2009, 8, "a", 8.1
insert test1 select 2009, 8, "b", 8.2
insert test1 select 2009, 9, "b", 9.1
insert test1 select 2010, 1, "a", 1.1
insert test1 select 2010, 2, "b", 2.1
insert test1 select 2010, 3, "b", 3.1
insert test1 select 2010, 4, "a", 4.1
insert test1 select 2010, 4, "b", 4.2
insert test1 select 2010, 5, "b", 5.1
go
/*******************************************************************/

Let's say we need to sum-up the sales numbers by months for each year in one SQL statement. Below are the 2 ways of achieving that using one SQL statement.

The recommended way (and enforced by Microsoft in their SQL Server) is to use the case statement with GROUP BY as follows:

/********************************************************/
select 
 year,
 m_1_sls = sum(case
                      when month=1  then sales
                      else null
                     end),
 m_2_sls = sum(case
                      when month=2  then sales
                      else null
                     end),
 m_3_sls = sum(case
                      when month=3  then sales
                      else null
                     end),
 m_4_sls = sum(case
                      when month=4  then sales
                      else null
                     end),
 m_5_sls = sum(case
                      when month=5  then sales
                      else null
                     end),
 m_6_sls = sum(case
                      when month=6  then sales
                      else null
                     end),
 m_7_sls = sum(case
                      when month=7  then sales
                      else null
                     end),
 m_8_sls = sum(case
                      when month=8  then sales
                      else null
                     end),
 m_9_sls = sum(case
                      when month=9  then sales
                      else null
                     end),
 m_10_sls = sum(case
                       when month=10  then sales
                       else null
                      end),
 m_11_sls = sum(case
                       when month=11  then sales
                       else null
                      end),
 m_12_sls = sum(case
                       when month=12  then sales
                       else null
                      end)
from test1
group by year
/****************************************************/

There is also less conventional way of achieving pretty much the same result - using "characteristic functions" as suggested by Anatoly Abramovich, Eugene Birger, and David Rozenshtein

/***********************************************************/
SELECT year, 
       m_1_sls=sum(
                          (1- ABS( SIGN( ISNULL( 1 - month, 1)))) * sales
                        ),
       m_2_sls=sum(
                          (1- ABS( SIGN( ISNULL( 2 - month, 1)))) * sales
                        ),
       m_3_sls=sum(
                          (1- ABS( SIGN( ISNULL( 3 - month, 1)))) * sales
                        ),
       m_4_sls=sum(
                          (1- ABS( SIGN( ISNULL( 4 - month, 1)))) * sales
                        ),
       m_5_sls=sum(
                          (1- ABS( SIGN( ISNULL( 5 - month, 1)))) * sales
                        ),
       m_6_sls=sum(
                          (1- ABS( SIGN( ISNULL( 6 - month, 1)))) * sales
                        ),
       m_7_sls=sum(
                          (1- ABS( SIGN( ISNULL( 7 - month, 1)))) * sales
                        ),
       m_8_sls=sum(
                          (1- ABS( SIGN( ISNULL( 8 - month, 1)))) * sales
                        ),
       m_9_sls=sum(
                          (1- ABS( SIGN( ISNULL( 9 - month, 1)))) * sales
                        ),
       m_10_sls=sum(
                          (1- ABS( SIGN( ISNULL( 10 - month, 1)))) * sales
                        ),
       m_11_sls=sum(
                          (1- ABS( SIGN( ISNULL( 11 - month, 1)))) * sales
                        ),
       m_12_sls=sum(
                          (1- ABS( SIGN( ISNULL( 12 - month, 1)))) * sales
                        )

from test1
group by year
/****************************************************/
The result of these SQLs will be:
yearm_1_slsm_2_slsm_3_slsm_4_slsm_5_slsm_6_slsm_7_slsm_8_slsm_9_slsm_10_slsm_11_slsm_12_sls
2009nullnullnullnullnull12.37.116.39.1nullnullnull
20101.12.13.18.35.1nullnullnullnullnullnullnull

Wednesday, October 27, 2010

Simple Command Line Parser Class

Here is a very simple class that allows to parse command line parameters.

This is how this class can be used:
...
CmdLineParameterReader cmdReader = new CmdLineParameterReader();
connParams.setHost(cmdReader.getParameterValue(argc,"-host"));
...
Hostname is saved in connParams class in this example.

You can pass hostname "myhost.net" as "-host myhost.net" or "-hostmyhost.net". Both cases are acceptable.


The class is defined as follows


package simple;

public class CmdLineParameterReader {

    String parameterMark = "";
    
    public CmdLineParameterReader(){
        
    }
    
    public CmdLineParameterReader(String parameterMarker){
        this.parameterMark=parameterMarker;
    }

    public String getParameterValue(String args[], String parameter) {
        String valOut = null;
        
        for(int i=0; i<args.length; i++){
            if( args[i].startsWith(parameter) ){
                if(args[i].equals(parameter)){
                    if(   (i+1) < args.length 
                       && ! "".equals(parameterMark)        
                       && ! args[i+1].startsWith(parameterMark)
                       ){
                        valOut = args[i+1];                        
                    }
                    else {
                        valOut = "";
                    }
                }
                else {
                    valOut = args[i].substring(parameter.length());
                }
            }
        }        
        return valOut;
    }    
}

Saturday, September 18, 2010

Kill Script Using Linux Shell (bash)

Here is a script written in bash that kills a process with the signature provided as a parameter.

NOTES:
- In RedHat Linux you can use /bin/sh as sh does not exist in Linux and it is pointing to bash instead
- In UBUNTU you need to specify /bin/bash as /bin/sh is pointing to dash by default
- I did not have a chance to test this script in other UNIX systems


#!/bin/bash

# Script name itself (we will ignore it, 
# as we do not want to kill our own script)
mysignature=$0

# Signature of a process to kill 
procsig=$1
echo Lookig to kill processes with signature :$procsig:

# Trim spaces 
read -rd '' param1 <<< "$procsig"

# Check that we suuplied a process signature, 
# and that it is not empty
if [ "$param1" = "" ]
then
  echo Need process name pattern
  exit 1
fi

echo Loking up process signature :$procsig:

# Get PID(s) of the process(es) to be killed
processes= \
 `ps auxwww | grep " $procsig" | grep -v grep | grep -v $mysignature | cut -c10-15`

if [ "$processes" = "" ]
then
  echo No process like  :$procsig: running
  exit 1
fi

echo Killing PIDs $processes

echo $processes | xargs -t kill

To run this script- create a file called (for example) stop_script.sh, copy the provided script into that file.

Run it (for example to kill all vi sessions) as follows

stop_script.sh "vi"

Sunday, September 12, 2010

Kill Script Using Perl

Sometimes you need to have a process that kills other processes.
Here is a small perl script that kills all the processes with a particular pattern in the name (" vi" in this case).


#!/usr/bin/perl

@pids = `ps auxwww | grep " vi" | grep -v grep | cut -c10-15`;

foreach(@pids){
  $killcmd = "kill -9 " . $_;
  print "executing " . $killcmd;
  `$killcmd`;
}

NOTES
- ps auxwww returns all running processes with full information known to the system

- grep " vi" finds processes with pattern " vi" in the output of ps

- grep -v grep excludes grep itself (we do not want to kill our grep) as the pattern we are looking for exists in grep, it will be picked up as just another process to be killed

-cut -c10-15 returns only PIDs

Once the list of PIDs is created we can loop through it killing processes (use -9 if you want to kill immediately interrupting IO).

Thursday, August 12, 2010

Java Deadlocking Example

Sometimes you need to test a piece of code that deals with deadlocking threads in Java. Below is a small program that deadlocks as soon as it starts.

A class that holds names of the threads


public class Constants {
  public static final String THREADNAME_1 = "Threadname 1";
  public static final String THREADNAME_2 = "Threadname 2"; 
}

The deadlocking class


import  java.util.concurrent.locks.*;

public class ThreadTestDeadlockingLock {

  Lock lock1 = new ReentrantLock();
  Lock lock2 = new ReentrantLock();

  Thread tr1 = new Thread(new runnableClass(), Constants.THREADNAME_1); 
  Thread tr2 = new Thread(new runnableClass(), Constants.THREADNAME_2);
 
  public ThreadTestDeadlockingLock(){
  }
 
  public static void main(String[] argc){

     ThreadTestDeadlockingLock threadtest = new ThreadTestDeadlockingLock();
  
     threadtest.tr1.start();
     threadtest.tr2.start();
  
   }

   /*
    * Runnable class definition
   */
   class runnableClass implements Runnable {
  
     public runnableClass(){
     }

     public void getLock1Lock2(){
        try{
           lock1.lock();
           System.out.println("Thread "+
                Thread.currentThread().getName() + " Locked 1");
           Thread.sleep(10);
           lock2.lock();
           System.out.println("Thread " +
                Thread.currentThread().getName() + " Locked 2");
           /*
            * Do work, do not release locks
            */
           Thread.sleep(1000);

        }
        catch(Exception ex){
           System.out.println("Exception in getLock1Lock2 " + 
                ex.toString());
        }
        finally {
            lock2.unlock();
            lock1.unlock();
        }
  }

  public void getLock2Lock1(){
      try{
          lock2.lock();
          System.out.println("Thread "+
               Thread.currentThread().getName() + " Locked 2");
          Thread.sleep(10);
          lock1.lock();
          System.out.println("Thread "+
               Thread.currentThread().getName() + " Locked 1");
          /*
           * Do work, do not release locks
           */
          Thread.sleep(1000);
       }
       catch (Exception ex){
         System.out.println("Exception in getLock2Lock1()" + ex.toString());
       }
       finally {
         lock1.unlock();
         lock2.unlock();
       }
  }

  public void run(){
  
     for(int i=0; i<100; i++){
         if(Constants.THREADNAME_1.equals(Thread.currentThread().getName())){
              getLock1Lock2();
         }
         else {
              getLock2Lock1();
         }     
      }   
  }
 } // End of internal class
}

Wednesday, July 28, 2010

Multithreading with nothing but Korn Shell

Multithreading usually brings C++ or Java to mind. But sometimes we need a "quick and dirty" system that will allow us to run multiple threads to do some work in the background - like database archiving, all sorts of cleanups, etc.
Below is a couple of Shell scripts that allow you to do just that - create multiple threads (or, rather, processes) with Parent thread (process) watching for children, and stopping the whole system when a child is having a problem. Here is the code with comments.


---------- PARENT ---------------

This is a parent shell script - the main thread, that controls all the kids.


#!/usr/bin/ksh

HOME_DIR=/host/linux_dev
LOG_DIR=${HOME_DIR}/logs

export HOME_DIR
export LOG_DIR

#
# Cleanup old files
#
rm ${LOG_DIR}/*

echo PARENTPID=$$ > ${LOG_DIR}/allPids.txt

total_children=3
sleep_before_start=1
sleep_check_interval=2

echo Starting ${total_children} children
i=0
while [[ $i -lt ${total_children} ]]
do
  sleep $sleep_before_start
  child $i >> ${LOG_DIR}/child_log_$i.txt 2>&1 &
  i=$((i+1))
done

sleep $sleep_before_start

. ${LOG_DIR}/allPids.txt

echo Children started, checking on them
while [[ $i -lt 1000 ]]
do
  sleep $sleep_check_interval
  j=0
  while [[ $j -lt ${total_children} ]]
  do
    . ${LOG_DIR}/CHILD_${j}_STATE
    if [[ ${CHILDSTATE} == 'DONE' ]]
    then
      echo CHILD $i is DONE
    elif [[ ${CHILDSTATE} == 'SUCCESS' ]]
    then
      eval "CHILDINSTANCEVAR=CHILD_${j}_PID"
      eval "CHILDINSTANCEPID=\$$CHILDINSTANCEVAR"
      echo "Checking CHILDINSTANCEPID=${CHILDINSTANCEPID}"
      CHILDINSTANCES=`ps auxwww | grep ${CHILDINSTANCEPID} | grep -v grep | wc -l`
      if [[ ${CHILDINSTANCES} -lt 1 ]]
      then
        echo "CHILD $j IS NOT DONE, BUT NOT RUNNING, Exiting"
        exit 1
      else
        echo "CHILD $j is running fine"
      fi
    else
      echo "CHILD $j needs attention, Exiting"
    fi
    j=$((j+1))
  done

  i=$((i+1))
done

------------------------------------ CHILD ----------------------------------

This is a child shell-script. Put the code below into a file called "child"



#!/usr/bin/ksh

THREAD_NUM=$1

echo CHILD_${THREAD_NUM}_PID=$$ >> ${LOG_DIR}/allPids.txt
# actual work

echo Starting doing actual work

cycles=10
sleep_interval=5

i=0
while [[ $i -lt $cycles ]]
do
  #
  # checkng if Parent is alive
  #
  . ${LOG_DIR}/allPids.txt
  PARENTINSTANCES=`ps auxwww | grep ${PARENTPID} | grep -v grep | wc -l`
  if [[ ${PARENTINSTANCES} -lt 1 ]]
  then
    echo "PARENT IS NOT RUNNING, Exiting"
    exit 1
  fi
  #
  # Doing work
  #
  rm ${LOG_DIR}/CHILD_${THREAD_NUM}_STATE
  echo "Working $(date): $i"
  echo "CHILDSTATE=SUCCESS" > ${LOG_DIR}/CHILD_${THREAD_NUM}_STATE
  sleep ${sleep_interval}
  i=$((i+1))
done

echo "CHILDSTATE=DONE" > ${LOG_DIR}/CHILD_${THREAD_NUM}_STATE
echo Thread $1 is DONE
-------------------------------------------------------------------------------
Here is how it all works

1. Parent thread starts children in the background in a loop (the number of children is controlled by total_children=3) by executing command as follows:


child $i >> ${LOG_DIR}/child_log_$i.txt 2>&1 &

each instance of a child is aware of its id (it accepts one parameter $i supplied by parent).


2. Children communicate with the parent by means of files. At the start, the parent writes its PPID into a file (allPids.txt), with every child adding its PPID to the same file. Once all children start, the file is sourced by the parent, as well as by all the children. The line of code in the parent where PPID is written is


echo PARENTPID=$$ > ${LOG_DIR}/allPids.txt


Children add their PPIDs to the communication file by running the command below


echo CHILD_${THREAD_NUM}_PID=$$ >> ${LOG_DIR}/allPids.txt


The resulting file looks as follows

PARENTPID=1234
CHILD_0_PID=1235
CHILD_1_PID=1236
CHILD_2_PID=1237
where 1234, etc. - are PPIDs for each "thread"

With all that done, children start doing their work with parent checking on their PPIDs periodically. If a parent sees that a child's process is not visible, it stops itself. Children check on parent's existence on every loop when they do their work.
If the parent process is not visible, the child stops itself.

Additionally, children can return status of their work into CHILD_${THREAD_NUM}_STATE file. If the status says "ERROR", for example, the parent can also stop, stopping all the children as a result.

In case of DB work (isql for SYBASE), children can return their work status by running something like "select 'CHILDSTATE='+@status", where status is defined by the results of the SQL executed. The status is written into a file that can be sourced and evaluated by a parent. A child can stop itself also, with the result of stopping all other children.

This program can be modified/improved, but serves as a good first step.

Feel free to use this script (or any part of it) anywhere you need.