MVSFORUMS.com

[Ranjish]

Hi,

We are trying to unload data from a partitioned table space with the following query.

select col1,col2
FROM table1 PART 1,2,3,4,5,6,7,8,9,10,11,12
WHERE col2 < (CURRENT TIMESTAMP - 30 MONTHS)
;

But we are getting the error

FU073 - FUNCTION: "PART XXX" INVALID WITH THIS SELECT --
FU074 --THIS FUNCTION NOT AVAILABLE VIA SQL ACCESS, WHICH THIS SELECT REQUIRES
FU075 - INVALID SELECT STATEMENT - SEE DOCUMENTATION

Our unload control card is as follows.

FASTUNLOAD
EXCP YES
INPUT-FORMAT TABLE
IO-BUFFERS 60
LOAD-CONTROL DB2LOAD
VSAM-BUFFERS 360
DISPLAY-STATUS 100000
SHRLEVEL CHANGE
SQL-ACCESS EXTENSION
SORTDEVT SORTWK SORTNUM 4 ESTIMATED-ROWS 1000000
OUTPUT-FORMAT DSNTIAUL

How can we solve this?

Note: when we remove the WHERE clause from the query, the unload is going fine.

regards
Ranjish

[kolusu]

Ranjish,

You need to change your sql access parameter to none. Try this sysin cards.

[Ranjish]

Hi Kolusu,

It worked. Thank you very much.
Could you please tell me what difference it makes bu putting SQL-ACCESS = NONE and why WHERE clause is not allowed with EXTENSION?

Thanks
Ranjish

[kolusu]

Ranjish,

Here is an explanation about SQL ACCESS.

SQL-ACCESS specifies the type of access Fast Unload should use to unload the data.

If you use SQL-ACCESS to unload table data, only one SELECT statement is processed at a time, in the order specified in the Fast Unload control cards. No other SELECT statement using VSAM, EXCP or SQL-ACCESS are processed at the same time. The SELECT statements using VSAM or EXCP are processed first, followed by the SQL-ACCESS SELECTs, which are processed in the order specified in the Fast Unload control statements.

SQL-ACCESS None means Do not use SQL access. If Fast Unload cannot process the SELECT
statement using VSAM or EXCP, an error message is issued and processing ends.This is the default. You can set the default for this keyword with the SQL-ACCESS parameter of the PFU member of highlvl.PARMLIB. You must specify NONE to unload data from image copies, concurrent copies, and DSN1COPYs.


SQL-ACCESS ONLY means use only SQL access to unload the tablespaces. This option lets you use any valid SELECT statement. Only one SELECT statement is processed at a time.The statements using VSAM or EXCP is processed first, followed by the SQL ACCESS SELECT statements, which is processed in the order specified in the Fast Unload control statements. You cannot include an ORDER-CLUSTERED clause, or the PART or OBID parameter with this option. You must specify SQLACCESS ONLY to use TRIM with ASCII objects. With SQL-ACCESS ONLY, DB2 determines locking. To preserve data integrity, you must specify LOCKSIZE TABLE on the tablespace.

SQL-ACCESS EXTENSION means use EXCP or VSAM processing if possible; otherwise use SQL
access. If you specify SQL-ACCESS EXTENSION, only those SELECT statements that require SQL access use it; all other SELECT statements are multi-tasked and unloaded using VSAM or EXCP. If you specify SQL-ACCESS EXTENSION with the PART or OBID parameter, and SQL-ACCESS is required, an error message is issued and processing terminates. Fast Unload performs the sort for an ORDER clause provided the ORDER specification uses column numbers or simple names. DB2 performs the sort for ORDER clauses that include column names with correlation variables.

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

[Ranjish]

Hi Kolusu,

Thank you very much for the detailed explanation.

I have one more question.

We have one job where unload is done from partitioned table space with SQL-ACCESS given as EXTENSION.

But only difference I could see from my previous control card is that
only one part was given in each SELECT query.

The query was like this.

SELECT COL1, COL2 FROM TABLE1 PART 1
WHERE
COL1 LIKE 'AB%'
;

SELECT COL1, COL2 FROM TABLE1 PART 2
WHERE
COL1 LIKE 'AB%'
;

Will this make any difference?

regards
Ranjish

[kolusu]

Ranjish,

In your example shown only one SELECT statement is processed at a time, in the order specified in the Fast Unload control cards. But in your very first sql you are clubbing all the partitions of the table in a single sql statement which is internally is combo of mutiple sql's. Each part consitute to a single a sql.SELECT statements are multi-tasked and unloaded using VSAM or EXCP.

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

[Ranjish]

Kolusu,

Thanks again. Its clear now.

regards
Ranjish