Hello all,
I have 2 very long list of elements. f.e.:
List 1
1 2 3 4 5
List 2
a b c
How can I combine the two with other. Like this:
1 a
1 b
1 c
2 a
2 b
2 c
3 a
etc.
Thank you in advance!
I made such construction, but it works very slow:(
declare -a itemid=($(cat ITEMID.txt))
declare -a elementen=($(cat elementen.txt))
for i in ${elementen[@]}
do
for b in ${itemid[@]}
do
db2 -x "select DOCID from ICMADMIN."$i" where ITEMID='"$b"'" && echo "$i"
done
done
You can save half of the memory by not storing in arrays.
set -f # for safety: no filename generation
for i in $(cat elementen.txt)
do
for b in $(cat ITEMID.txt)
do
db2 -x "select DOCID from ICMADMIN."$i" where ITEMID='"$b"'" && echo "$i"
done
done
bash even allows to replace $(cat ...) with $(< ...) .
mapfile -td" " F1 < file1
mapfile -td" " F2 < file2
IFS=$'\n'
for i in ${F1
[*]}
do db2 -x <<< ${F2
[*]/#/select DOCID from ICMADMIN.$i where ITEMID= }
done
db2 -x
select DOCID from ICMADMIN.1 where ITEMID= a
select DOCID from ICMADMIN.1 where ITEMID= b
select DOCID from ICMADMIN.1 where ITEMID= c
db2 -x
select DOCID from ICMADMIN.2 where ITEMID= a
select DOCID from ICMADMIN.2 where ITEMID= b
select DOCID from ICMADMIN.2 where ITEMID= c
db2 -x
select DOCID from ICMADMIN.3 where ITEMID= a
select DOCID from ICMADMIN.3 where ITEMID= b
select DOCID from ICMADMIN.3 where ITEMID= c
db2 -x
select DOCID from ICMADMIN.4 where ITEMID= a
select DOCID from ICMADMIN.4 where ITEMID= b
select DOCID from ICMADMIN.4 where ITEMID= c
db2 -x
select DOCID from ICMADMIN.5 where ITEMID= a
select DOCID from ICMADMIN.5 where ITEMID= b
select DOCID from ICMADMIN.5 where ITEMID= c
I would think that you are spending a lot of time in your loop repeatedly opening the database connection. Could you build all the queries into a single command file and execute one DB read? The output could be caught to a file or into a variable and then processed further. You might need to add extra columns to the output so you can tell which is from which query if that's necessary.
Can you tell us more about the overall aim of the process and perhaps we can help you towards a more efficient solution. The variable names don't give much away. Perhaps more meaning ful names (even if it takes marginally longer to type) will be far clearer to understand later on.
Hello, thank you for your help.
the command eval echo -en "db2' '-x' \"'select' 'DOCID' 'from' \"'ICMADMIN.{$(tr ' ' , <ITEMID.txt)}'\" where ITEMID=\"'{$(tr ' ' , <et.txt)}'\n'"
make an output:
If 'A1001001A18K15A90630F85271' is not a typo you can run the following command to lookup the package that contains the binary:
command-not-found A1001001A18K15A90630F85271
-bash: A1001001A18K15A90630F85271: command not found
echo -x "select DOCID from "ICMADMIN.1" where ITEMID="a
echo -x "select DOCID from "ICMADMIN.1" where ITEMID="b
echo -x "select DOCID from "ICMADMIN.1" where ITEMID="c
echo -x "select DOCID from "ICMADMIN.2" where ITEMID="a
echo -x "select DOCID from "ICMADMIN.2" where ITEMID="b
echo -x "select DOCID from "ICMADMIN.2" where ITEMID="c
echo -x "select DOCID from "ICMADMIN.3" where ITEMID="a
echo -x "select DOCID from "ICMADMIN.3" where ITEMID="b
echo -x "select DOCID from "ICMADMIN.3" where ITEMID="c
...
--- Post updated at 12:54 ---
Is the file correct? <et.txt
--- Post updated at 13:02 ---
But please do not take my examples too seriously. Such lines are very difficult to debug and maintain.
You are pursue practical goals, and therefore it is better to pay attention to #2 and #6 post. IMHO
I think the main problem of a large execution time is precisely in the number of calls to the DB2 command
eval echo -ne "db2' '-x' \"'select' 'DOCID' 'from' 'ICMADMIN.{$(paste -sd, et.tst)}' where ITEMID='\'{$(paste -sd, itid.tst)}\''\"\n'"
db2 -x "select DOCID from ICMADMIN.NNSPDM_PRI_01001 where ITEMID='A1001001A19D02A60150B20570'"
db2 -x "select DOCID from ICMADMIN.NNSPDM_PRI_01001 where ITEMID='A1001001A19D02A60150B20572'"
db2 -x "select DOCID from ICMADMIN.NNSPDM_PRI_01001 where ITEMID='A1001001A19D02A60150B20574'"
db2 -x "select DOCID from ICMADMIN.NNSPDM_PRI_01001 where ITEMID='A1001001A19D02A60150E20578'"
db2 -x "select DOCID from ICMADMIN.NNSPDM_PRI_01001 where ITEMID='A1001001A19D02A60150E20576'"
db2 -x "select DOCID from ICMADMIN.NNSTDM_DOC_04001 where ITEMID='A1001001A19D02A60150B20570'"
but if I try do
bash <(eval echo -ne "db2' '-x' \"'select' 'DOCID' 'from' 'ICMADMIN.{$(paste -sd, et.tst)}' where ITEMID='\'{$(paste -sd, itid.tst)}\''\"\n'")
SQL1024N A database connection does not exist. SQLSTATE=08003
SQL1024N A database connection does not exist. SQLSTATE=08003
SQL1024N A database connection does not exist. SQLSTATE=08003
SQL1024N A database connection does not exist. SQLSTATE=08003
SQL1024N A database connection does not exist. SQLSTATE=08003
SQL1024N A database connection does not exist. SQLSTATE=08003
but the db2 connection is almost exists.
If I take a command with cut/paste i got no errors.
+ join -12 -22 -o 1.1,2.1 et.tst itid.tst
+ read a b
+ db2 -x 'select DOCID from ICMADMIN.NNSPDM_PRI_01001 where ITEMID='\''A1001001A19D02A60150B20570'\'''
SQL1024N A database connection does not exist. SQLSTATE=08003