1 Something about DataStage, DataStage Administration, Job Designing,Developing, DataStage troubleshooting, DataStage Installation Configuration, ETL, DataWareHousing, DB2, Teradata, Oracle and Scripting Nuts Bolts of DataStage Home Interview Questions DataStage Scenarios Series Posts EBooks About Me !! Sunday, May 18, 2014 DataStage Scenario Problem > DataStage Scenario Problem7 Solution Design : a) Job Design : Below is the design which can achieve the output as we needed Here, we are reading seq file as a input, then data is passing through a Transformer and Peek stage to achieve the output b) Transformer Stage Properties Stage will run SEQUENTIALLY The logic here is give a seq no of each 3 rows so that we can do vertical pivot on that For that we will use 3 stage variable which derivation is as : svCntr+1 = svCntr DataStage Scenario Design7 job1 Total Pageviews 1 4 5 4 6 1 9 Search Try Me DataSet in DataStage Issuing commands to a Queue Manager (runmqsc) Hash Files in DataStage XMeta DB : Datastage Repository InfoSphere DataStage Jobstatus returned Codes from dsjob Conductor Node in Datastage Schema File in Datastage Sort stage to remove duplicate 14 Good design tips in Datastage Datastage Coding Checklist Must Reads 1 More Next Blog» Create Blog Sign In 2 svIncmt = svPivot If mod(svCntr,3)=0Then svIncmt+1 Else svPivot = svIncmt and use below column derivation : In_xfmcol = col svPivot = PivotCol First stage variable (svCntr) is increment by 1 for each input row starting from 1 Second stage variable svPivot is assigned to PivotCol The third stage variable is counting the input rows, when it is equal to multiple of 3, it increment the svIncmt by 1 By this we will get the output like Col PivotCol a 1 b 1 c 1 d 2 e 2 f 2 g 3 b) Pivot Stage Properties Stage will run SEQUENTIALLY Now, we have to use Vertical Pivoting on Col Group by PivotCol , Put Array Size to 3 Get daily dose of Tech Food Email address Submit DataStage4You 111 have us in circles View all Follow tech foodies ▼ 2014 (103) ► October (7) ► September (9) ► August (5) ► July (12) ► June (10) ▼ May (13) Surrogate Key Generator Create/Update State File Surrogate Key Generator Create State File Some Oracle SQL should known by Developer 1 Interview Questions : DataWareHouse Part6 DataStage Scenario Design8 job1 Data Warehouse Testing Checklist DataStage Scenario Design7 job1 DataStage Scenario Design6 job1 What ETL is not? Framework ( usually followed ) in ETL Testing Blog Archive 3 Newer Post Older PostHome Now, Map the column to output file and compile the job For More > VISIT THIS LINK By ETL DataStage at 22:51 2 Comments Labels: Code, DataSet, DataStage, design, develop, function, input, Job, output, pivot, problem, run, scenario, Seq File, sequential, transformer, vertical 2 Comments DataStage4You Login Sort by Best Share ⤤ Join the discussion… • Reply • Harini • 5 months ago What if the Array Index is not known at the time of the compliation In that case how the vertical pivoting can be achieved? △ ▽ • Reply • datastage4you • 5 months agoMod > Harini Hi Harini As Array Index is not supporting a parameter in it Tha value of it is varies from 1 to 1000 for variable no of column, you have to follow a alternative ( which can also be hectic ) 1 Have a idea of max no of rows per key and create the job with that index 2 get the no of rows per KEY column 3 Insert the difference of rows with dummy data 4 use the pivot and removed the dummy data I am not saying this is a optimize way but this can be a solution if data is low We need to look out for another solution if data load is too high △ ▽ Subscribe✉ Add Disqus to your sited Privacy Favorite ★ Share › Share › ETL Testing : Trends Challenges ETL Testing : Approach DataStage Scenario Problem19 ► April (10) ► March (9) ► February (16) ► January (12) ► 2013 (167) ► 2012 (175) ► 2011 (8) Administration application authorities client Code column commands Concept Configuration create Data database DataSet DataStage DataWareHouse DB2 DBMS debug delete design develop difference director Documentation dsenv dsjob DSRPC environment Errors ETL file function Information input install Interview Job keys Link Linux list Logging Logical logs lookup managers message queue Metadata Model MQ names Optimizing Oracle output Parallel parameter partition performance Physical port problem process Project Putty Questions remove Tags Cloud PH 421 advantage Agents aggregator Answers architecture ASB attribute backup basic binary block books Buffer certification change channel checkpoint cleanup clear Column Generator compiler Conceptual conductor container copy counter Crontab deadlock deploy dimension Dimensional DSparam dump duplicate encrypt engine exception execution export fact factless FAQ FileSet filter free ftp fun fundamentals granularity Guest hadoop handling hash head hide horizontal Host huge hyperlink import increase index issue istool Java jdbc join leaders listener load local locks Login macro mail maintenance memory merge modify Monitor MQSC multiple NLS node notes notification odbc odbcini operator orchadmin ORLogging orphan OS osh package Parallelism password peek Perl phantom pivot player Practices profile programming purge read registry reject release report Resource Restart Roles 4 Subscribe to: Post Comments (Atom) routine rows scenario Schema Script Seq File sequence Server Service Setting Shell shell scripting sort source SQL stages Start Stop surrogate table target teradata tips tool transformer Troubleshoot Tutorial Unix User Utility UV variables warnings WAS websphere windows XMETA row generator RTLogging run sample SCD scheduler score Scratch section session Share shortcuts show slowly snowflake solution space SSH Standards Star statistics status storage switch system tail temporary time trace transformation trigger tuning type unique uvodbcconfig version videos view Vincent McBurney Virtual write Write Range Map xml z/OS The postings on this site are my own and don't necessarily represent IBM's or other companies positions, strategies or opinions All content provided on this blog is for informational purposes only The owner of this blog makes no representations as to the accuracy or completeness of any information on this site or found by following any link on this site The owner will not be liable for any errors or omissions in this information nor for the availability of this information The owner will not be liable for any losses, injuries, or damages from the display or use of his information // Disclaimer Did you find this Blog helpful ?? Let me know wwwfacebookcom/datastage4you Ethereal template Powered by Blogger