I was trying to create a report from the table which has more than 3 million rows, after starting reloading the script the process stop in between saying the virtual memory is full , so i stopped the process and re-wriiten the script for limited time , just tried to run the data for couple of months instead of taking all the data from table, but i am not able to run the script, it just hangs...
it says executing scripts but no progress is seen, i deleted the application and created all over again , but still no progress...
could any one please explain what happened and what should i do now for running the script?
My guess is that your SQL Server is having difficulties performing a SELECT on a large table with no index on a date. Try creating an index and running the same query. Also, check that your syntax with regards to the date representation is correct.
how do i select last 1000 rows instead of first 1000 rows?
when i right select statement something like
select * from dw_t_jobdata where start_time > '01-jun-2010' , i get an error saying start_time not found, if i just write a select statement without where condition it works..i basically want recent data ...how do i get that...
I think your initial issue is memory related, ideally you should have 1gb of RAM available (physical memory) for every 1-2 million rows you plan to load. This needs to be on the machine that is doing the loading.
If you are getting the error "start_time" not found that suggests there is no field called start_time or the format of the date you supplied in the where clause did not match the format of the date in the table.
if start_time is definately a valid field, it may be easier to rewrite it as
select * from dw_t_jobdata where start_time = max(start_time);
and if that works, you can expand the dataset by changing it to
select * from dw_t_jobdata where start_time = max(start_time)+30;
I know QV is case sensitive in the the majority of the load script but can't rememebr off hand if that includes the SQL select statment. I say this as field name is being designated in the select as "START_TIME" (all uppercase), therefore stands to reason it should be uppercase in the where clause.
I tried below query , but i get an error saying invalid number , if i use max i get an error saying cannot use group funtion , if i use the date in single quotes like '06/01/2010' the scripts gets hanged ....Please Help
SQL
SELECT
TO_CHAR(START_TIME,'MM/DD/YYYY')
AS
START_DAY,
TO_CHAR(FINISH_TIME,'MM/DD/YYYY')
AS
FINISH_DAY,
"JOB_ID",
"USER_NAME",
"QUEUE_TIME",
"START_TIME",
"FINISH_TIME",
"PROJECT_NAME",
"QUEUE_NAME",
"CLUSTER_NAME",
"LSFHOST_MODEL",
"HOST_NAME",
"LSFHOST_TYPE",
"MEM_REQUEST",
"MEM_USAGE",
"SWAP_USAGE",
"RUN_TIME",
"CPU_MINUTES",
"PENDING_TIME",
"JOB_EXIT_STATUS",
"JOB_EXIT_CODE",
"JOB_NAME",
"JOB_ARRAY_INDEX",
"JOBRES_RAW",
"JOB_CMD"
FROM
PIOWNER."DW_T_JOBDATA" WHERE TO_CHAR(START_TIME,'MM/DD/YYYY') > 06/01/2010
The function "to_char" is converting the date to a string, i am not sure you want to be loading it in already pre-converted as QV should be handling as much of the data manipulation as possible outwith the select statment. String functions also tend to be more of a strain on SQL servers than straight numerical functions.
however, if you keep the to_char, you should be able to enter a wildcard match such as
PIOWNER."DW_T_JOBDATA" WHERE TO_CHAR(START_TIME,'MM/DD/YYYY') like '06/%'
to return everything for the 6th month although doesn't give you any control over the year.
i forgot to say you need to use a subquery for the where clause if you are usuing max, try
SQL SELECT START_TIME, USER_NAME from PIOWNER."DW_T_JOBDATA" where START_TIME = (select max(START_TIME) from PIOWNER."DW_T_JOBDATA")
and if that works, add fields one by one to the main query
If you have a PL/SQL editor such as TOAD it would be worth fine tuning the select statement with that first, then you can rule any syntax issues out.
