if exists ( select  1  from sysibm.systables where rtrim(creator)||'.'||rtrim(name) = 'ASOUP.TMP_ARENDA' with ur ) drop table ASOUP.TMP_ARENDA;

CREATE TABLE asoup.tmp_arenda
 (VAG_NUM    INTEGER         NOT NULL,
  AREN_DATEN  DATE,
  AREN_DATEK  DATE
 );

connect to datacane user az using azz1234;

export to as.tmp.ixf of ixf
select 
       integer(aren.vagon),
	   cast(substr(char(aren.daten), 1 , 4 ) ||'-'|| substr(char(aren.daten), 5 , 2 ) ||'-'|| substr (char (aren.daten), 7 , 2 ) as date),
	   cast(substr(char(aren.datek), 1 , 4 ) ||'-'|| substr(char(aren.datek), 5 , 2 ) ||'-'|| substr (char (aren.datek), 7 , 2 ) as date)	   	   
from t_layer.dbf_arenda aren;

connect reset;

connect to MMV user db2admin using  123444 ;

drop table as.ak;
drop table as.tmp;

CREATE TABLE as.tmp
 (VG    INTEGER         NOT NULL,
  AREN_DATEN  DATE,
  AREN_DATEK  DATE
 );

CREATE TABLE AS.AK
 (VG    INTEGER         NOT NULL,
  URV        CHARACTER( 2 )    NOT NULL,
  TARA       SMALLINT        NOT NULL,
  STR        SMALLINT        NOT NULL,
  DOR        SMALLINT        NOT NULL,
  OWN_ID     INTEGER,
  AREN_ID    INTEGER,
  AREN_DATE  DATE
 );
  
import from as.tmp.ixf of ixf
insert into as.tmp;

insert into as.ak (VG, URV, TARA, STR, DOR, OWN_ID, AREN_ID, AREN_DATE)
select VG, URV, TARA, STR, DOR, OWN_ID, AREN_ID, AREN_DATE from as.akdor;

alter table AS.AKDOR
activate not logged initially with empty table;

insert into as.akdor (VG, URV, TARA, STR, DOR, OWN_ID, AREN_ID, AREN_DATE)
select vag.VG, vag.URV, vag.TARA, vag.STR, vag.DOR, vag.OWN_ID, vag.AREN_ID, aren.AREN_DATEK 
from as.ak vag
left join (Select VG, max(AREN_DATEK) AREN_DATEK from as.tmp group by VG) as aren on
	 vag.VG_NUM = aren.VG;

drop table as.ak;
drop table as.tmp;

connect reset;

cd % 1    

db2 -stvf sql\load_arend.sql >>report\load_arend.log

cd % 1    


db2 -tvf sql\load_arend.sql >>report\load_arend.log