Tuesday, July 3, 2012

Generate dummy data using SQL on DB2 LUW


Not sure where I got this query, but is very handy for generating sample data, try this.

-- columns SSN,FIRST_NAME,LAST_NAME,JOB_CODE,DEPT,SALARY,DOB  
WITH TEMP1 (s1,r1,r2,r3,r4) AS   (
VALUES
(0   ,RAND(2)   ,RAND()+(RAND()/1E5)   ,RAND()* RAND()   ,RAND()* RAND()* RAND())    
UNION
ALL   SELECT
s1 + 1   ,
RAND()   ,
RAND()+(RAND()/1E5)   ,
RAND()* RAND()   ,
RAND()* RAND()* RAND()  
FROM
TEMP1  
WHERE
s1 < 10 --rows   )   SELECT
SUBSTR(DIGITS(INT(r2*988+10)),
8) ||'-'||           SUBSTR(DIGITS(INT(r1*88+10)),
9) || '-' ||           TRANSLATE(SUBSTR(DIGITS(s1),
7),
'9873450126',
'0123456789'),
CHR(INT(r1*26+65))|| CHR(INT(r2*26+97))|| CHR(INT(r3*26+97))||CHR(INT(r4*26+97))|| CHR(INT(r3*10+97))|| CHR(INT(r3*11+97)),
CHR(INT(r2*26+65))||TRANSLATE(CHAR(INT(r2*1E7)),
'aaeeiibmty',
'0123456789'),
CASE            
   WHEN INT(r4*9) > 7 THEN 'MGR'            
   WHEN INT(r4*9) > 5 THEN 'SUPR'            
   WHEN INT(r4*9) > 3 THEN 'PGMR'            
   WHEN INT(R4*9) > 1 THEN 'SEC'            
   ELSE 'WKR'          
END,
INT(r3*98+1),
DECIMAL(r4*99999,
7,
2),
DATE('1930-01-01') + INT(50-(r4*50)) YEARS + INT(r4*11) MONTHS + INT(r4*27) DAYS  
FROM
TEMP1

About Me

By profession I am a Database Administrator (DBA) with total 13 yrs. of experience in the field of Information Technology, out of that 9 yrs as SQL DBA and last 3 years in IBM System i/iSeries and DB2 LUW 9. I have handled Developer, plus production support roles, and I like both the roles. I love and live information technology hence the name "Techonologyyogi" Apart from that I am a small, retail investor, with small investments in India and United States in the form of Equity holdings via common stocks. Don't ask me if I have made money, I have been loosing money in stocks.