TekBytz

Migrating Oracle Pivot Function to DB2/DASHDB

by on Aug.08, 2017, under Database

I have come across a situation where I need to migrate a ORACLE query which has a pivot function to DB2 equivalent SQL. The Pivot function is not available in DB2 or DASHDB. so was no direct solution in the internet and hence came up with my own solution

Sample Oracle Query with pivot function :

SELECT NM,DESC,ADM,USR,SUP
FROM
(
SELECT NM, DESC, GRP
FROM SRC_GRP
)
PIVOT
(
COUNT(GRP)
FOR GRP IN
(
'Admin' as ADM,
'User' as USR,
'Support' as SUP
)
)
ORDER BY NM

 

Converted equivalent DB2/DASHDB query:

SELECT NM,DESC,count(ADM) AS ADM,count(USR) AS USR,count(SUP) AS SUP
FROM
(
SELECT NM, DESC,
CASE WHEN GRP = 'Admin' THEN GRP END AS ADM,
CASE WHEN GRP = 'User' THEN GRP END AS USR,
CASE WHEN GRP = 'Support' THEN GRP END AS SUP
FROM SRC_GRP
)
group by NM, DESC
Order by NM

 

I was able to get the same output from both the queries.

Leave a Comment :, , , , , , more...

Datastage : Schema reconciliation detected a size mismatch in timestamp

by on Jul.31, 2017, under Error and Issues

Error Message: 

Schema reconciliation detected a size mismatch for column DT. When writing column DATETIME(fraction=6) into database column DATETIME(fraction=0), truncation, loss of precision, data corruption or padding can occur.

 

Resolution : 

This issue occurs when the source stream is read as times tamp with microseconds and when writing into target the time stamp field does not have microseconds scale.  There is no direct functions to truncate the microseconds scale, as a work-around below method can be used.

StringToTimestamp(TimestampToString(from_xfm.DT,"%yyyy-%mm-%dd %hh:%nn:%ss") ,"%yyyy-%mm-%dd %hh:%nn:%ss")

Leave a Comment :, , , , more...

SQL0668N Operation not allowed for reason code “3” on table

by on Jul.24, 2017, under Error and Issues

Error:

SQLExecute reported: SQLSTATE = 57016: Native Error Code = -668: Msg = [IBM][CLI Driver][DB2/LINUXX8664] SQL0668N Operation not allowed for reason code “3” on table “XXX.XXXX”. SQLSTATE=57016

 

Resolution:

After doing several research the issue was the table was marked in check pending state due to bad bulk load without terminate in load statement.

Loading a null file to the table with the terminate command will clear the issue.

load from /dev/null of del terminate into XXX.XXXX

Sample :

db2 => LOAD FROM /DEV/NULL OF DEL TERMINATE INTO XXX.XXXX
SQL3110N The utility has completed processing. "0" rows were read from the
input file.


Number of rows read = 0
Number of rows skipped = 0
Number of rows loaded = 0
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 0

 

Platform :

Datastage 11.5 on Linux &

DB2 10.5 on Linux

Leave a Comment :, , , , more...

Datastage : DB2 Lookup Stage : SAX parser exception thrown: The input ended before all started tags were ended

by on Jun.30, 2017, under Error and Issues

Error :

Unrecognized argument: variant=’9.1\’,
library=ccdb2,
version=1.0,
variantlist=\’V1;9.1::ccdb2\’,
versionlist=\’1.0\’,
name=DB2Connector
}’

SAX parser exception thrown: The input ended before all started tags were ended. Last tag started was ‘before’ (CC_PropertySet::fatalError, file CC_PropertySet.cpp, line 2,236)

 

Resolution:

There are lot of references in google that the similar kind of error occurred in the XML stage and it it XML stage related error.  But in my case this occurred in the DB2 lookup stage. After serval analysis, I found that, there was a environment variable defined for the table name and during the migration the environment variable was not added in the job parameter.

example :

SELECT COL1, COL2  FROM #$SrcTbl#

$SrcTbl was missing in the job parameter list.

This looks to weird since if this was in DB2 source or target stage, the error will be table not found.

 

Platform :

Datastage 11.5 on Linux

 

 

Leave a Comment :, , more...

DataStage : DB2 Stage : Failure during execution of operator logic

by on Jun.30, 2017, under Error and Issues

Error Details :
Failure during execution of operator logic.
Output 0 produced 0 records.
APT_Decimal::assignFromString: invalid format for the source string. Expecting trailing NUL, space, or tab character(s) got ‘E’

Resolution:
One of the column which has the SQL type Decimal has the value “5E+7” which was not able convert to Decimal value. To eliminate this error read the column as String

This could happen when the decimal field is computed. To revert back to decimal field use explicit cast function

CAST(CAST(DISBURSED_AMOUNT AS FLOAT) AS DECIMAL(31,2))

Platform : 

DataStage 11.5 running on linux
DASHDB 10.5 running on linux

Leave a Comment :, , , more...

Live Currency Conversion For Multi-Currency E-Commerce Websites

by on Apr.15, 2016, under Internet, Links, Programming

If your E-Commerce website requires to display the product value in multiple currency based on user’s choice, it requires a conversion from base currency. This piece of utility will directly do the currency conversion based on live exchange rates. This utility used from google finance currency conversion module to perform the conversion and provide the output value.

Utility Usage:
http://currency.tekbytz.com/?amount=<amount>&from=<from currency>&to=<to currency>

Sample Usage:

http://currency.tekbytz.com/?amount=10&from=USD&to=INR

http://currency.tekbytz.com/?amount=20&from=CAD&to=EUR

http://currency.tekbytz.com/?amount=25&from=MYR&to=INR

The below currency format/codes are supported

  • United Arab Emirates Dirham (AED)
  • Afghan Afghani (AFN)
  • Albanian Lek (ALL)
  • Armenian Dram (AMD)
  • Netherlands Antillean Guilder (ANG)
  • Angolan Kwanza (AOA)
  • Argentine Peso (ARS)
  • Australian Dollar (A$)
  • Aruban Florin (AWG)
  • Azerbaijani Manat (AZN)
  • Bosnia-Herzegovina Convertible Mark (BAM)
  • Barbadian Dollar (BBD)
  • Bangladeshi Taka (BDT)
  • Bulgarian Lev (BGN)
  • Bahraini Dinar (BHD)
  • Burundian Franc (BIF)
  • Bermudan Dollar (BMD)
  • Brunei Dollar (BND)
  • Bolivian Boliviano (BOB)
  • Brazilian Real (R$)
  • Bahamian Dollar (BSD)
  • Bitcoin (฿)
  • Bhutanese Ngultrum (BTN)
  • Botswanan Pula (BWP)
  • Belarusian Ruble (BYR)
  • Belize Dollar (BZD)
  • Canadian Dollar (CA$)
  • Congolese Franc (CDF)
  • Swiss Franc (CHF)
  • Chilean Unit of Account (UF) (CLF)
  • Chilean Peso (CLP)
  • CNH (CNH)
  • Chinese Yuan (CN¥)
  • Colombian Peso (COP)
  • Costa Rican Colón (CRC)
  • Cuban Peso (CUP)
  • Cape Verdean Escudo (CVE)
  • Czech Republic Koruna (CZK)
  • German Mark (DEM)
  • Djiboutian Franc (DJF)
  • Danish Krone (DKK)
  • Dominican Peso (DOP)
  • Algerian Dinar (DZD)
  • Egyptian Pound (EGP)
  • Eritrean Nakfa (ERN)
  • Ethiopian Birr (ETB)
  • Euro (€)
  • Finnish Markka (FIM)
  • Fijian Dollar (FJD)
  • Falkland Islands Pound (FKP)
  • French Franc (FRF)
  • British Pound (£)
  • Georgian Lari (GEL)
  • Ghanaian Cedi (GHS)
  • Gibraltar Pound (GIP)
  • Gambian Dalasi (GMD)
  • Guinean Franc (GNF)
  • Guatemalan Quetzal (GTQ)
  • Guyanaese Dollar (GYD)
  • Hong Kong Dollar (HK$)
  • Honduran Lempira (HNL)
  • Croatian Kuna (HRK)
  • Haitian Gourde (HTG)
  • Hungarian Forint (HUF)
  • Indonesian Rupiah (IDR)
  • Irish Pound (IEP)
  • Israeli New Sheqel (₪)
  • Indian Rupee (Rs.)
  • Iraqi Dinar (IQD)
  • Iranian Rial (IRR)
  • Icelandic Króna (ISK)
  • Italian Lira (ITL)
  • Jamaican Dollar (JMD)
  • Jordanian Dinar (JOD)
  • Japanese Yen (¥)
  • Kenyan Shilling (KES)
  • Kyrgystani Som (KGS)
  • Cambodian Riel (KHR)
  • Comorian Franc (KMF)
  • North Korean Won (KPW)
  • South Korean Won (₩)
  • Kuwaiti Dinar (KWD)
  • Cayman Islands Dollar (KYD)
  • Kazakhstani Tenge (KZT)
  • Laotian Kip (LAK)
  • Lebanese Pound (LBP)
  • Sri Lankan Rupee (LKR)
  • Liberian Dollar (LRD)
  • Lesotho Loti (LSL)
  • Lithuanian Litas (LTL)
  • Latvian Lats (LVL)
  • Libyan Dinar (LYD)
  • Moroccan Dirham (MAD)
  • Moldovan Leu (MDL)
  • Malagasy Ariary (MGA)
  • Macedonian Denar (MKD)
  • Myanmar Kyat (MMK)
  • Mongolian Tugrik (MNT)
  • Macanese Pataca (MOP)
  • Mauritanian Ouguiya (MRO)
  • Mauritian Rupee (MUR)
  • Maldivian Rufiyaa (MVR)
  • Malawian Kwacha (MWK)
  • Mexican Peso (MX$)
  • Malaysian Ringgit (MYR)
  • Mozambican Metical (MZN)
  • Namibian Dollar (NAD)
  • Nigerian Naira (NGN)
  • Nicaraguan Córdoba (NIO)
  • Norwegian Krone (NOK)
  • Nepalese Rupee (NPR)
  • New Zealand Dollar (NZ$)
  • Omani Rial (OMR)
  • Panamanian Balboa (PAB)
  • Peruvian Nuevo Sol (PEN)
  • Papua New Guinean Kina (PGK)
  • Philippine Peso (Php)
  • PKG (PKG)
  • Pakistani Rupee (PKR)
  • Polish Zloty (PLN)
  • Paraguayan Guarani (PYG)
  • Qatari Rial (QAR)
  • Romanian Leu (RON)
  • Serbian Dinar (RSD)
  • Russian Ruble (RUB)
  • Rwandan Franc (RWF)
  • Saudi Riyal (SAR)
  • Solomon Islands Dollar (SBD)
  • Seychellois Rupee (SCR)
  • Sudanese Pound (SDG)
  • Swedish Krona (SEK)
  • Singapore Dollar (SGD)
  • St. Helena Pound (SHP)
  • Slovak Koruna (SKK)
  • Sierra Leonean Leone (SLL)
  • Somali Shilling (SOS)
  • Surinamese Dollar (SRD)
  • São Tomé & Príncipe Dobra (STD)
  • Salvadoran Colón (SVC)
  • Syrian Pound (SYP)
  • Swazi Lilangeni (SZL)
  • Thai Baht (THB)
  • Tajikistani Somoni (TJS)
  • Turkmenistani Manat (TMT)
  • Tunisian Dinar (TND)
  • Tongan Paʻanga (TOP)
  • Turkish Lira (TRY)
  • Trinidad & Tobago Dollar (TTD)
  • New Taiwan Dollar (NT$)
  • Tanzanian Shilling (TZS)
  • Ukrainian Hryvnia (UAH)
  • Ugandan Shilling (UGX)
  • US Dollar ($)
  • Uruguayan Peso (UYU)
  • Uzbekistani Som (UZS)
  • Venezuelan Bolívar (VEF)
  • Vietnamese Dong (₫)
  • Vanuatu Vatu (VUV)
  • Samoan Tala (WST)
  • Central African CFA Franc (FCFA)
  • East Caribbean Dollar (EC$)
  • Special Drawing Rights (XDR)
  • West African CFA Franc (CFA)
  • CFP Franc (CFPF)
  • Yemeni Rial (YER)
  • South African Rand (ZAR)
  • Zambian Kwacha (1968–2012) (ZMK)
  • Zambian Kwacha (ZMW)
  • Zimbabwean Dollar (2009) (ZWL)

Source : Google Finance

Leave a Comment :, , , , more...

How to run a background command in script as a parameter in unix shell scripts

by on Jun.21, 2013, under Programming, Unix

Many times you would have tried running a command or script directly in a unix script by providing the name and path of the script. Also you would have run scripts are commands by assingning to variables. But by any chance if you had tried running a script or command assingned to variables you might got struck and which also gives you a vauge error message.

For example consider a script param.sh as below
 

echo $1
$1

And you are running the scipt as

./param.sh 'ls -ltr &'

You would be getting a error message

./& not found

This issue can be resloved by using eval command in param.sh and the new script looks as below.

eval $1

eval command will take an argument and construct a command of it, which will be executed by the shell. It is an inbuit command and there is the man page http://www.unix.com/man-page/posix/1posix/eval/

Leave a Comment :, , , more...

How to find a substring between Nth and (N+1)th occurance of a charecter in a cell in Excel

by on Jun.21, 2013, under Office, Windows

Excel’s built-in functions can do some pretty clever stuff, but unfortunately Excel has no single function that will return the  string beween nth and n+1th charecter occurrence of specified data. Fortunately, there are ways to make Excel do this. The function used could be complex but could be accomblished.

Consider if you have the text “How to find the substring in Excel” in cell A1 and incase if you need to find 5th word in the string the below formula can be used. This can be used for strings with any seperators

=IF(ISERR(MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),4))+1,(FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),5))-FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),4))-1))),"",MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),4))+1,(FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),5))-FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),4))-1)))

 As a Generic formula the below paramters can be changed to achive to find any word between occurances

=IF(ISERR(MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"C",CHAR(1),n))+1,(FIND(CHAR(1),SUBSTITUTE(A1,"C",CHAR(1),m))-FIND(CHAR(1),SUBSTITUTE(A1,"C",CHAR(1),n))-1))),"",MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"C",CHAR(1),n))+1,(FIND(CHAR(1),SUBSTITUTE(A1,"C",CHAR(1),m))-FIND(CHAR(1),SUBSTITUTE(A1,"C",CHAR(1),n))-1)))

 Where

A1 – Cell of the string

C – is the Charecter occurance, can be ” “, “.” etc

n – Nth occurance of Charecter C, n starts from 0

m – N+1 occurance of Charecter C, m starts from 0

 Few other useful formulas that would handle strings.

Finding the Nth position of a charecter

=FIND(CHAR(1),SUBSTITUTE(A1,”i”,CHAR(1),3))

This formula will inf the 3rd occurance if charecter “i” in cell A1.

Alternte formula with similar function

=SEARCHB("i",A1,(SEARCHB("i",A1,(SEARCHB("i",A1,1)+1))+1))

Finding the first Word in a String

=IF(ISERR(MID(A1,1,FIND(" ",A1)-1)),"",MID(A1,1,FIND(" ",A1)-1))

This formula will find the 1st word in a sting in cell A1.

Leave a Comment :, more...

Bypassing websense using proxy

by on Mar.27, 2012, under Internet

In many of the corporate and government institutions usage of internet is restricted using content filtering software’s like websense. Websense is commonly used software for content filtering and monitoring the internet activity so as to restrict the employees visiting the internet sites while at work. Websense is designed to block the site content in several categories like adult content, entertainment, drugs,  games, sports, peer-to-peer sharing, gambling, instant messaging, emails, health, illegal, job search, religion, travel, violence, weapons, advertisements, freeware, software download, pay to surf, malicious website and many more. The software also tracks individual internet usage for the purpose of collecting and reporting on any browsing deviating from the standards set by the library, government or other employer, and its reports can be data drilled by risk class, category, URL, application, user, workstation, dates, and more

The concept behind Websense is simple, whenever an employee attempts to visit a specific webpage; a request is generated and then passes through a firewall. Websense looks at this request and it allows or disallows the request depending on if the requested URL is in the Websense database. In transparent mode, Websense counts on the fact that the firewall will forward the whole request in a single time. If the whole request is not transferred at one time, Websense will allow the packet to pass, as the packet doesn’t look like a HTTP request. If you want to bypass Websense and browse any page, you can use several techniques to bypass Websense’s filtering and authentication process.

 

Bypassing Websense using a Web Proxy

One method used to bypass Websense is to use a web-based proxy site. With a web proxy, Websense sees you browse to the web proxy and not to the web site you are actually browsing to. You can browse to the web based proxy site, and then the web proxy browses to the web site you actually wanted to visit. The reason that these web proxies are able to bypass Websense is the ‘S’ in the HTTP address (https://) stands for secure connection and Websense does not block such secured connections. This is probably the simplest way to get around the Websense program as it takes little time and no one will wonder what you are up to.  To find a current web proxy, visit proxy page. 

 

Creating your own Web proxy

The constraint with the above practice is that the web based proxy site could be blocked under the category “proxy” in websense because these are commonly used proxy site. The alternate approach is that you can build your own proxy site if can afford for a web hosting. PHP Web proxy is an open source tool that can be deployed in your website and enable your website for web proxy access. Here the constraint is that you website could be blocked in any of the other categories mentioned above. For this you could make your website as HTTPS enable using RAPIDSSL or any of the cheap or free SSL certificates.

Bypassing Websense using HTTP Tunneling

You can also bypass Websense by using HTTP Tunneling. HTTP Tunneling programs work well to bypass Websense. Most programs also come with options, which help you to destroy the history of your Internet and Widows activity. What this means is that you can ensure comfort and security of your online activities and can spoof your web browser information. One can download HTTP Tunneling software programs from the Internet for free or inexpensively. HTTP Tunnel software consists of client-server HTTP Tunneling applications that integrate with existing application software, permitting them to be used in conditions of restricted network connectivity including firewall networks, networks behind proxy servers, and NATs.

 

Leave a Comment :, more...

Recreating corrupted wireless network profile in Vista

by on Sep.03, 2010, under Windows

If Windows Vista prompts you to enter the Wirless password everytime after reboot even after you save the password, you might want to know something is wired with Windows Wireless Management.  Wireless Management is not saving your passwords and matter of fact not even the profile. You can verify it by going to Network and Sharing > Manage Wireless Networks option.

This means your Wireless profile setting is corrupt and it is better to recreate so that Windows Vista starts saving the wireless profile and password. Follow below steps to recreate the profiles.

  1. Goto Windows Services and Stop WLAN AutoConfig service.
  2. In regedit find the following key and delete all the sub keys under it.
    HKEY_LOCAL_MACHINESOFTWAREMicrosoftWlansvc
  3. Now go back to Windows services and restore the WLAN AutoConfig service
  4. Add the wireless profiles again and save the password. The wireless profiles will start appearing in your Manage Wireless window.

Hope this has helped your resolve the issue. If you have any questions please let me know.

1 Comment :, , more...

Looking for something?

Use the form below to search the site:

Still not finding what you're looking for? Drop a comment on a post or contact us so we can take care of it!