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 (
(0 ,RAND(2) ,RAND()+(RAND()/1E5) ,RAND()* RAND() ,RAND()* RAND()* RAND())
s1 + 1 ,
RAND()* RAND() ,
RAND()* RAND()* RAND()
s1 < 10 --rows ) SELECT
8) ||'-'|| SUBSTR(DIGITS(INT(r1*88+10)),
9) || '-' || TRANSLATE(SUBSTR(DIGITS(s1),
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)),
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'
DATE('1930-01-01') + INT(50-(r4*50)) YEARS + INT(r4*11) MONTHS + INT(r4*27) DAYS
Posted by Technology Yogi at 10:56 PM
- Technology Yogi
- 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.