How to implement threads in this example?

Question

Perl

Good afternoon, at work I had to deal with Perl and it really fascinated me. There is a task of archiving data in the Oracle database, which I was able to implement with current knowledge by fastening the execution of the .sql file through sqlplus. Now archiving occurs sequentially, but I want to parallelize it so as not to produce a bunch of files, I want to use threads in the existing code. But apparently, I ran a little ahead and it’s still difficult for me to comprehend some things, so I ask for your help in editing my finished code.
The order of operation is as follows: when the sql file is launched, a record is made in the database about the beginning of the archiving process (let's call it a log), then in a cycle archiving is started for each of the database schemas specified in the array and a log is written, after the cycle is completed, a log is written about the execution archiving.
Actually, I want to run what is in the loop in parallel.
It was possible to launch via thread, async, but then the main script did not wait for the execution of the child ones ...
Thank you!

#!/usr/bin/perl -w

use strict;
use DBI;
use DBD::Oracle;

my $archdate= 201506; #Ручная подстановка периода архивации

#Подключение к БД
my $dbh = DBI ->connect( 'dbi:Oracle:xe', 'hr', '159', {RaiseError => 1, AutoCommit => 1} );

my @connetction_log = ('user1/[email protected]','user2/[email protected]','user3/[email protected]');

#Записываем начало выполнения арихвации в БД
my $sql = " INSERT INTO MS_ARCH_LOG VALUES ('HR ONE', '$archdate', SYSDATE, 0, 3,'0%', 'RUNNING', NULL) ";
my $sth = $dbh->prepare( $sql );
$sth->execute();


my $i;

foreach $i (@connetction_log) {
#С этого момента начинаем создавать потоки
    system ("sqlplus", $i, "\@arc_data.sql", "$archdate");
    
    my $sql = " UPDATE MS_ARCH_LOG SET DONE = DONE + 1, PCNT = ROUND((DONE+1)/TOTAL*100)||'%' WHERE DB = 'HR ONE' AND PERIOD = '$archdate' ";
    my $sth = $dbh->prepare( $sql );
    $sth->execute();
}
# Продолжаем выполнение скрипта когда все потоки закончили работу
$sql = " UPDATE MS_ARCH_LOG SET STATUS = 'DONE', COMPLITED = SYSDATE  WHERE DB = 'HR ONE' AND PERIOD = '$archdate' ";
$sth = $dbh->prepare( $sql );
$sth->execute();

Answer 1

why didn't the main script wait for execution through thread?
there is such a thing as join

Answer 2

In your case, I would look at https://metacpan.org/pod/AnyEvent::DBI.
And it is not recommended to use streams in perl.

Answer 3

The easiest way is to run three copies of the archive script, each of which will record the completion time. The last one completed will record the time when the entire backup was completed.