RPG not RPGLE — but the first issues would exist under any circumstances
Ok, today is 20120117, unless you are somewhere where it isn’t.
1. is that the format of PAYDATE CCYYMMDD ?
2. When you say 90 days do you really mean 3 months or precisely 90 days correcting for month length and leap year
Could it just be the first of a month. (hint first of the month will be simple math).
if we run the purge today would your accept 20111001 — Nov 1 2011?
Phil

Do you have to do this with RPG?
SQL would be faster.

Just so I understand
Input file is Payments and has 11 million records
You want to move all records from that file where the date is greater that 90 days from the current date to a file call PAYMENTSH.
Once completed the combined record count will be 11 million records.
Correct?

The date in Payments is defined as 8S.
Is it mmddyyyy?

A source member with SQL statements ‘INSERT into PAYWMENTS…..
Then DELETE from PAYMENTS ….
can be setup. Then you just need to do a single RUNSQLSTM.

LMK if this is correct and the date format an some SQL statements can be provided to you.

All questions asked so far are valid. And I’d agree that this would probably be better done with pure SQL than with RPG, no matter if it’s ILE or not. This is straightforward data manipulation with no program logic needed.

The structure of the “date” field in PAYMENTS must be the first thing that is needed.

Tom

So 1 PayDate is CCYYMMDD, great.

2 since the group is going to recommend SQL any date is easily computed, but when spec’s say 90 days they rarely mean 90 days, they often mean 3 months or the end or beginning of a month. Your previous response on this appears to be cut off.
Phil

Phil, sorry the format of PAYDATE is yyyymmdd  (20081020).

Yes century year month day, you are right, I should have said YYYYMMDD
20120117

but what of the 90 days
2 since the group is going to recommend SQL any date is easily computed, but when spec’s say 90 days they rarely mean 90 days, they often mean 3 months or the end or beginning of a month. Your previous response on this appears to be cut off.

And the field structure of the two files are the same.

Ok friends, today is 20120117 correct ?
I need on file PAYMENT only these records maybe need convert this date to julian date first :

Phil, Yes are the same structure.

There is probably an easier way SQL is

Insert into PAYMENTH
Select * from PAYMENT

where date(substr(digits(paydate),1,4) concat ‘-’ concat
substr(digits(payment),5,2) concat ‘-’ concat
substr(digits(payment),7,2)) < (curdate() – 90 days)

OK, but I need to clean on PAYMENT these records copied ON PAYMENTH

where date(substr(digits(paydate),1,4) concat ‘-’ concat
substr(digits(payment),5,2) concat ‘-’ concat
substr(digits(payment),7,2)) < (curdate() – 90 days)

Since {(curdate() – 90 days)} will essentially be constant during the execution and since {paydate} is in a useful YYYYMMDD form, consider reversing the WHERE clause idea.

That is, consider simply converting the digits from {(curdate() – 90 days)} to a numeric value. Then make a direct comparison to {paydate}. I suspect that the optimizer might than only execute a single conversion.

Tom

I need to clean on PAYMENT these records copied ON PAYMENTH

The thought from CharlieBrowne was that there will eventually be two SQL statements.

The first one will be the INSERT statement that Phil is helping you with. Once that statement is decided for certain, it will have an embedded SELECT that has a WHERE clause. The statement that Phil has already shown will probably work. I suggest that some testing be done first in order to understand performance and other possible implications.

The second SQL statement will be a DELETE statement. It will delete from PAYMENTS, and it will have the same WHERE clause as is used for the SELECT.

The INSERT runs first. The selected rows get inserted into PAYMENTSH. Then the DELETE will run, and it will delete the same set of rows from PAYMENTS that was inserted into PAYMENTSH.

For a test, I would try something like {(curdate() – 2191 days)} first. That should only select rows that are old enough to go almost all the way back into 2005 (if I multiplied correctly). With a smaller set of rows, you might verify that the right rows are being inserted before running over the full set. You would also get an idea of how long the process might run over the full set. You can clear those rows out of PAYMENTSH before running the final SQL.

Once tests are finished, you can change {(curdate() – 2191 days)} to be {(curdate() – 90 days)}. At that time, you would add the DELETE statement with the final form of the WHERE clause.

When the two statements run together, rows will be inserted into PAYMENTSH; and the same set of rows will be removed from PAYMENTS.

Tom

Excellent as always Tom. Reversing the conversion might speed up the process, although, the record insertion and deletion would be about 99% of the work.

Make sure that there is a backup of the file before you begin!

Friends, now it´s works.

1º
Select count(*) from PAYMENT (11.922.568 records).
2º
Insert into PAYMENTH
Select * from PAYMENT
where date(substr(digits(paydate),1,4) concat ‘-’ concat
                      substr(digits(paydate),5,2) concat ‘-’ concat
                      substr(digits(paydate),7,2)) < (curdate() - 90 days) (11.846.288 records).
3º
delete from PAYMENT
where date(substr(digits(paydate),1,4) concat ‘-’ concat
                      substr(digits(paydate),5,2) concat ‘-’ concat
                      substr(digits(paydate),7,2)) < (curdate() - 90 days) (11.846.288 records).
4º
select * from PAYMENT (76.280 records).

Now on my historical file have 11.846.288 records when I need to use.

Thanks for all again.
PutzGrilla

Friends, now it´s works.

Does PAYMENTS need to be reorganized now? If it had 11 million records deleted, it might need it.

Tom

Yes Tom thanks, the command RGZPFM was executed on the file PAYMENT.

PutzGrilla.

Hi friends, I am using from an AS/400 the date values are stored as a 7 digit numeric. Here is the format: "CYYMMDD" C is a "century digit" where 0 = 1900 and 1 = 2000.
How can I subtract 90 days from the System Date too ?

Thanks,
RBDSILVA

You didn’t specify the environment that you needed the 7 digit date in
This RPGLE code populates a 7 digit numeric with date + 90 days.

MyDate7 = %dec(%char(%date() + %days(90) : *cymd0) : 7 : 0)

add – subtract what’s a few days between friends?

MyDate7 = %dec(%char(%date() – %days(90) : *cymd0) : 7 : 0)

Hi Phil, sorry ...do You have a SQL/400 example ?

I was thinking something like

select substring(rcpdat,4,2) CONCAT '-' CONCAT        
           substring(rcpdat,6,2) CONCAT '-' CONCAT        
 (cast(substring(rcpdat,1,3) as integer) + 1900) as   
 date from lib/file < (CURDATE() - 90 DAYS)

But... not works.

RBDSILVA

Thanks, Putzgrilla, no works to me too.

This code should be good till 2039 to convert current date – 90 days into a 7 digit number. Please post another message in 2039.
Phil

select dec('1' || substring( char(date(curdate() -90 days)),7,2)     
                         || substring( char(date(curdate() -90 days)),4,2)     
                         || substring( char(date(curdate() -90 days)),1,2) )   
from sysibm/sysdummy1                 

Hi friends, I have the solution...

INSERT INTO BACKUPLIB/FILEX
SELECT * FROM LIB/FILEX WHERE
date((cast(substring(field,1,3) as integer) + 1900)
                                        CONCAT '-' CONCAT
     substring(field,4,2) CONCAT '-' CONCAT
     substring(field,6,2)) < (CURDATE() - 90 DAYS);
delete from LIB/FILEX WHERE
date((cast(substring(field,1,3) as integer) + 1900)
                                        CONCAT '-' CONCAT
     substring(field,4,2) CONCAT '-' CONCAT
     substring(field,6,2)) < (CURDATE() - 90 DAYS);

Thanks for all.

Works but as Tom pointed out, I think this is going to be way more efficient.

INSERT INTO BACKUPLIB/FILEX
SELECT * FROM LIB/FILEX WHERE
field < dec(’1′ || substring( char(date(curdate() -90 days)),7,2)
|| substring( char(date(curdate() -90 days)),4,2)
|| substring( char(date(curdate() -90 days)),1,2) );

Yes Phil, You are correct, is more efficient and more fast too.

I´m changing now.

Thanks,
Roberto

Cool, did you determine that it’s actually running faster?
We thought it would be a bit faster if the SQL engine recognized that it can use the first extract of the current date for all rows.
Phil