Systems Engineering and RDBMS

Archive for February 10th, 2007

Grouping on a weekly basis – Oracle

Posted by decipherinfosys on February 10, 2007

This is a requirement that I faced a few hours ago from one of our clients.  They have a table in which orders get tracked in the system and they needed to write a report in which they can group the data for their shipping date and see the output on a weekly basis i.e. how many orders were shipped in a particular week – they needed this information for the past 7 months.  In the actual report, this information was then combined with other information to show the drill-down into specific sales regions, specific sales people, territories, product categorization etc..  But, here is the simple query that can do the aggregation per week and show the data to the end user (this was for Oracle):

/*
Name of the table changed to preserve client schema information
*/

select trunc(ship_date_time,’w’) WEEK_OF, count(*)
from table_x
where stat_code = 90   /*the status input – shipped orders only*/
and ship_date_time > ? /*the criteria input*/
group by trunc(ship_date_time,’w’)
/

Sample abridged output:

WEEK_OF     COUNT(*)
——— ———-
01-JUN-06      22370
08-JUN-06      23648
15-JUN-06      20676
22-JUN-06      21083
01-JUL-06      18085
08-JUL-06      17871
15-JUL-06      23504
22-JUL-06      23465
29-JUL-06      13072
01-AUG-06      20685
08-AUG-06      19462

Posted in Oracle | Leave a Comment »

Importing DTS packages in SQL Server 2000

Posted by decipherinfosys on February 10, 2007

In SQL Server 2000, if you have a requirement to copy/transfer multiple DTS packages from one instance to the other, doing it manually becomes cumbersome. There are utilities in the market that do that for you but what if you have to make this import a part of your install? If you have such a requirement, then you can save your DTS packages as files and then write up a simple VB Script and call it through cscript to load up the packages. You can put this process into your install shield code to give a better end user experience.

/******************************************************************************/
‘ Usage: cscript ImportPackages.vbs <sql server name> <sql server userid> <sql server password>

‘ Example: cscript ImportPackages.vbs microndt2978-2 sa password

And here is the code for the ImportPackages.vbs (we use this in the case of slot_info in order to load the DTS packages):

option explicit

‘——————————————————————-
‘ ImportPackages.vbs — import DTS packages from .dts files.

‘ Usage: cscript ImportPackages.vbs <sql server name> <sql server userid> <sql server password>

‘ Example: cscript ImportPackages.vbs microndt2978-2 sa password

‘ This script imports all packages from the folder specified below to the
‘ specified SQL Server.

‘——————————————————————-

const PACKAGE_FOLDER = “DTS Packages”

dim oPackage
dim oFile
dim sPath
dim iCount
dim sPackageName
dim sServer
dim sUserid
dim sPassword
dim oArgs

‘ Validate arguments.
set oArgs = wscript.Arguments

if oArgs.count < 3 then
wscript.echo “Usage: cscript ImportPackages.vbs <sql server name> <sql server userid> <sql server password>”
wscript.quit 1
end if

sServer = oArgs(0)
sUserid = oArgs(1)
sPassword = oArgs(2)

‘ Obtain the path that the script is run in. The DTS package folder
‘ is relative to that path.
sPath = left(wscript.scriptfullname, len(wscript.scriptfullname) – len(wscript.scriptname))

dim fso
set fso = CreateObject(“Scripting.FileSystemObject”)

dim oFolder

‘ Make sure the package folder exists.
if not fso.FolderExists(sPath & PACKAGE_FOLDER) then
wscript.echo “Can’t find package folder ” & sPath & PACKAGE_FOLDER
wscript.quit 1
end if

set oFolder = fso.GetFolder(sPath & PACKAGE_FOLDER)

iCount = 0

‘ Iterate through the package files.
for each oFile in oFolder.Files
wscript.echo “Importing ” & oFile.Path
set oPackage = CreateObject(“DTS.Package2”)

‘ Get the package name, which is the name of the .dts file
‘ without the .dts extension.
sPackageName = left(oFile.Name, len(oFile.Name) – 4)

‘ Delete the package, but don’t error out if it already doesn’t exist.
on error resume next
oPackage.RemoveFromSQLServer sServer, sUserid, sPassword, , , , sPackageName
on error goto 0

‘ Load the package from the .dts file.
oPackage.LoadFromStorageFile oFile.Path, “”

‘ Save the package to SQL Server.
oPackage.SaveToSQLServer sServer, sUserid, sPassword

iCount = iCount + 1

‘ Release the package object. It can’t be reused.
set oPackage = Nothing
next

wscript.echo “Successfully imported ” & iCount & ” packages.”
wscript.quit 0
/******************************************************************************/
An excellent article on transferring the DTS packages in SQL 2000 is available at Darren Green’s site:

http://www.sqldts.com/204.aspx

Posted in SQL Server | Leave a Comment »

Monitoring number of connections in Oracle

Posted by decipherinfosys on February 10, 2007

Here is a shell script to monitor the number of connections in the database (count, who and where).

This script takes in 3 arguments:

1) oracle_sid:  The Oracle SID
2) no. of times: Number of times you want to loop. Next argument sleep time should be considered when setting this value
3) sleep time: Number of seconds you want to sleep.

This scripts assumes that you can connect to the DB as sysdba.  If you don’t have the privilege, modify the script to connect as a different user.
/*************************************************************************/

#!/bin/ksh

if [[ “$#” -ne “3” ]]; then
print “Usage: ${0} <OracleSid> <No. of times> <sleep time>”
exit -1
fi

oracleSid=${1}
loopTimes=${2}
sleepTime=${3}

export ORACLE_SID=${oracleSid}

counter=0

if [[ ! -d “./log” ]]; then
mkdir log
else
if [[ -f ./log/session_count.log ]]; then
rm -f ./log/session_count.log
fi
fi

exec >> ./log/session_count.log 2>&1

while [[ “${counter}” -le “${loopTimes}” ]];
do
print “Connection info at: $(date)”
print
print
sqlplus -s /nolog << EOF
set feed off pages 1000 lines 120 echo off verify off
rem connecting as sysdba assuming you have logged in as Oracle
rem change this if you don’t have the privilege
rem example: connect user/password@oracle_sid
connect / as sysdba

column username heading ‘User|Name’ format a30
column machine heading ‘Machine’    format a30
column session_count heading ‘Session|Count’ format 999G999

select s.username as username,
(case
when grouping(s.machine) = 1 then
‘**** All Machines ****’
else
s.machine
end) AS machine,
count(*) as session_count
from v\$session s,
v\$process p
where s.paddr   = p.addr
and s.username is not null
group by rollup (s.username, s.machine)
order by s.username, s.machine ;
EOF

sleep ${sleepTime}
counter=$(expr ${counter} + 1)
print
print
done

exit
/*************************************************************************/

Posted in Oracle | Leave a Comment »

Checking for Oracle Client Version on Windows

Posted by decipherinfosys on February 10, 2007

There are many different ways of finding out the client version of Oracle on Windows but if you are using the registry or looking for the physical folders to ascertain that, you are in for a shock when the version changes (even with updates in the same release set like, 9.2.0.x).  This becomes important for application development shops since they have to write an install package and the install scripts need to check and log which version of the client is running and if that version of the client is not supported by that release version of the product, then appropriate action needs to be taken by that code.

Some people use SQL *PLUS or some other dlls to determine the client version but depending upon how the install was done, those client tools or dlls may not be installed (say the Administrative mode was not chosen and only the runtime mode was chosen during the client installation process).  Moreover, the version information for those dlls do not always reflect the right client version.  Here is a sure-shot way of ascertaining the client version on a windows box and this does not change with the release of the Oracle client:

This can be done by just executing tnsping and piping the output to a file and parsing that file for the version info.  tnsping will get installed with each client install regardless of the install options choosen and it’s version is the same as the Oracle client dll versions.  So this is what we can do:

Have a text file dynamically created with the version banner in it:
a. create a batch file called version.bat
b. put 1 line in test.bat:
tnsping > version.txt
c. execute version.bat
d. version.txt will have your version string in it

In the event of multiple Oracle homes, the one that is set as the default is the one that will be shown in this version information.

Posted in Oracle | Leave a Comment »