I have a requirement where I shall have to create a view to pull last 2 years worth of data from a table which has lots of history. Here is my view definition.
CREATE VIEW View1 AS SELECT * FROM TAB1 WHERE UDATE >= CAST((YEAR(CURRENT DATE) - 2) || '0000' AS DEC(8,0))
The view is created without any issues and I can run the Select against the view and I'm seeing the results.
However, something weird going on when I use the above view in an SQL program to join against another table. The Select statement has Distinct and Order By clause as well.
When I run the program it gets stuck on the Fetch statement forever. The job doesn't makes any progress but when I see the Threads (Option 20) from Work Job there is a thread which is constantly spinning the Total CPU and Aux I/O.
Any idea what might be going in the background.
CREATE VIEW View1 AS SELECT * FROM TAB1 WHERE UDATE >= CAST((YEAR(CURRENT DATE) - 2) || '0000' AS DEC(8,0))
The view is created without any issues and I can run the Select against the view and I'm seeing the results.
However, something weird going on when I use the above view in an SQL program to join against another table. The Select statement has Distinct and Order By clause as well.
When I run the program it gets stuck on the Fetch statement forever. The job doesn't makes any progress but when I see the Threads (Option 20) from Work Job there is a thread which is constantly spinning the Total CPU and Aux I/O.
Any idea what might be going in the background.
Comment