Create VIEW in Datastage

ds2000 · Post by **ds2000** » Thu Jun 11, 2009 1:06 pm

How can a VIEW be created in datastage ? I created a view in db and then tried to INSERT data into it using db2 api stage but datastage id was not allowed for insert in a view. Is it right way to do it.

VIEW SQL:
----------------
Create VIEW as MYVIEW
COL1, COL2
From TBL

chulett · Post by **chulett** » Thu Jun 11, 2009 1:29 pm

It's not an updateable view so no-one is allowed to insert records into it, not just DataStage. Have a chat with your DB2 DBA to see what you'd need to do to accomplish that.

ray.wurlod · Post by **ray.wurlod** » Thu Jun 11, 2009 4:51 pm

Did the CREATE VIEW statement complete successfully?

Usually a CREATE VIEW statement includes a SELECT clause.

Read the DB2 documentation about how to create updatable views.

ds2000 · Post by **ds2000** » Thu Jun 11, 2009 9:55 pm

It was typo, I missed it in the message. Just wanted to know if its the right approach that first create a view and then LOAD it with datastage job from different extracts ?

ray.wurlod · Post by **ray.wurlod** » Thu Jun 11, 2009 10:21 pm

It's an unusual approach, but it's not wrong per se provided you create an updatable view - which implies that there are already extant tables (on which the view is based) that can actually store the data you push into the view.

chulett · Post by **chulett** » Thu Jun 11, 2009 11:43 pm

As noted, it is unusual as you would typically load the base tables directly. Why the perceived need to target a view?