Need help executing DS35

[Maze999]

I am exploring ds35 for the first time and trying to use if for MSSQL DB(Linux) following the below steps.

(a) Install database system (OS + Oracle or SQL Server or MySQL)
(b) Download DVDStore3 to database system
(c) Ensure that Perl is setup on system
(d) Run InstallDVDStore.pl
(e) Use scripts created by InstallDVDStore.pl to create and load database
(f) On system to be used as driver, run CreateConfigFile.pl
(g) Run driver program with the parameter file created by CreateConfigFile.pl

Client: Windows 2019
DB server: MSSQL (Linux)-SLES15SP4

Following the documentation I have downloaded the ds35 to the DB server and executed the Install_DVDStore.pl

---

Initializing parameters...

---

Database Size: 14
Database size is in GB
Database Type is MSSQL
System Type for DB Server is LINUX
File Paths : /home/DS3-SQL/

---

Calculating Rows in tables!!
Large size database ( greater than 1 GB)
Ratio calculated : 0.14
Customer Rows: 28000000
Order Rows / month: 1400000
Product Rows: 140000

Creating CSV files....
Starting to create CSV data files....
For larger database sizes, it will take time.
Do not kill the script till execution is complete.
…..

Scripts that are created from template files have '_' 14 GB in their name.

User can edit the sql script generated for customizing sql script for more DBFiles per table and change the paths of DBFiles.

Now Run CreateConfigFile.pl perl script in ds3 folder which will generate configuration file used as input to the driver program.

Can you please help me with the sequence of scripts to be executed for the step “Use scripts created by InstallDVDStore.pl to create and load database”?

  Under ds35/sqlserverds35/build I see sqlserverds35_create_all_init_14GB.sql and ran that , DS3 database got created.
  Should it be followed by sqlserverds3_createtables.sql? , perl scripts under load looks to be meant for windows.

Apart from executing locally on the DB server how can we build and execute from the windows client as mentioned in the documentation? is it only applicable between windows client and windows DB host or can be done between windows client and Linux host acting as DB server
“The database creation has been almost completely automated in order to accomplish
the above mentioned parallelization and also to make it easier to setup and use.
It is designed to be done from a remote client or driver system that is not the
database host. It is still possible to run it all locally on the database system
if desired, although a remote driver is recommended.”

[toddmuirhead]

Happy to help you get things going.

I have not tried running DVD Store for SQL Server on a Linux system recently. I have used a windows client with a linux based SQL Server database successfully - so I know that it will work. However, the DVDStore kit doesn't currently create a .sh linux style set of scripts for the SQL Server version. If you are wanting to use a windows client ultimately - it will likely be easier to simply begin using a windows client now. You will just need to install SQL Server Client for Windows (MsSQLCmdLnUtils.msi) , an ODBC required component (msodbdsql.msi) and Strawberry Perl for windows (strawberry-perl-5.32.1.1-64bit.msi or current version). Then you will be able to run the resulting .bat script from windows client.

In the ds35/sqlserverds35 directory there should be a file - sqlserverds35_create_all_concurrent_14GB.bat .

It's contents should look something like this and show the order of running the scripts in the build and load directories. You could try running these on the Linux system, but there are some windows style paths in them that will cause them to break. So running from windows client will be much easier:

REM remote_sqlserverds3_create_all_concurrent.bat
REM start in ./ds3/sqlserverds3
REM syntax is: remote_sqlserverds3_create_all_concurrent.bat
cd build
sqlcmd -S %1 -U sa -P password -i sqlserverds35_create_all_init_3GB.sql
perl sqlserverds35_perl_create_db_tables_multi.pl %1 %2
cd ..\load
perl ds35_create_sqlserver_multistore_load_files.pl %1 %2
perl ds35_execute_sqlserver_multistore_load.pl %1 %2
cd ..
REM sleep 60
cd build
sqlcmd -S %1 -U sa -P password -i sqlserverds35_shrinklog.sql
perl sqlserverds35_perl_create_indexes_multi.pl %1 %2
perl sqlserverds35_perl_create_sp_multi.pl %1 %2
sqlcmd -S %1 -U sa -P password -i sqlserverds35_create_user.sql
cd ..

Please let me know if you have any issues or more questions.

Thanks,
Todd

[Maze999]

Thank you Todd, after installing Full-Text Search i was able to execute the .bat script from windows client without any issues but execution of ds35sqlserverdriver.exe is failing with Thread 0: User user15891235 not found, thread exiting

Can you please take a look and let me know your inputs?

.bat execution succeeded:
E:\DS3\ds35-main\sqlserverds35\build>perl sqlserverds35_perl_create_sp_multi.pl 172.x.x.x 14
A subdirectory or file 172.18.6.127 already exists.
...

Changed database context to 'DS3'.
...
Changed database context to 'DS3'.

E:\DS3\ds35-main\sqlserverds35\build>sqlcmd -S 172.x.x.x -U sa -P password -i sqlserverds35_create_user.sql
Changed database context to 'master'.

ds35sqlserverdriver.exe fails:
PS E:\DS3\ds35-main> .\sqlserverds35\ds35sqlserverdriver.exe --config_file=DriverConfig.txt
Total number of Threads to be Spawned across multiple servers are n_threads: 1
target= 172.x.x.x n_threads= 1 ramp_rate= 10 run_time= 0 db_size= 24GB warmup_time= 1 think_time= 0
pct_newcustomers= 20
pct_newmembers= 1 n_searches= 3 search_batch_size= 5 search_depth= 500 n_reviews=3 pct_newreviews=5
pct_newhelpfulness=10
n_line_items5 virt_dir= ds2 page_type= php windows_perf_host= detailed_view= N linux_perf_host=
output_file= ds2_mode= N n_stores= 1 log_freq= 10 log_timestamp= NONE

Using WIN32 QueryPerformanceCounters for measuring response time

Performance Counter Category Test and Counter MaxRT exist
Thread 0: created for User 0
Thread 0: connected to 172.x.x.x
Controller (3/6/2024 4:33:52 AM): all threads running
Controller (3/6/2024 4:33:52 AM): all threads connected - issuing Start
Thread 0: User user15891235 not found, thread exiting
et= 10.0 n_overall=1 opm=5 rt_tot_lastn_max_msec=176 rt_tot_avg_msec=176 rt_tot_sampled=176 rollbacks: n=0 %= 0.0
threads[0].ThreadState= Stopped
et= 20.0 n_overall=1 opm=2 rt_tot_lastn_max_msec=176 rt_tot_avg_msec=176 rt_tot_sampled=0 rollbacks: n=0 %= 0.0
threads[0].ThreadState= Stopped
et= 30.0 n_overall=1 opm=1 rt_tot_lastn_max_msec=176 rt_tot_avg_msec=176 rt_tot_sampled=0 rollbacks: n=0 %= 0.0
threads[0].ThreadState= Stopped
et= 40.0 n_overall=1 opm=1 rt_tot_lastn_max_msec=176 rt_tot_avg_msec=176 rt_tot_sampled=0 rollbacks: n=0 %= 0.0
threads[0].ThreadState= Stopped
et= 57.0 n_overall=1 opm=1 rt_tot_lastn_max_msec=176 rt_tot_avg_msec=176 rt_tot_sampled=0 rollbacks: n=0 %= 0.0
threads[0].ThreadState= Stopped
et= 67.0 n_overall=1 opm=0 rt_tot_lastn_max_msec=176 rt_tot_avg_msec=176 rt_tot_sampled=0 rollbacks: n=0 %= 0.0
threads[0].ThreadState= Stopped
Stats reset
....

Please let me know if any further details are needed

[toddmuirhead]

Maze,

This error literally means that the named user - user15891235 - is not found in the database. This means that either the database did not load correctly during the create_all script or you have specified db_size for the driver program to be larger than what was loaded.

The output shows that you have set db_size to 24GB. In your initial question you had specified 14GB - so this might be your problem. What size did you specify when you ran InstallDVDStore.pl?

You will need to specify the same size that you used in InstallDVDStore.pl in your config_file - DriverConfig.txt - which I'm guessing is 14GB.

If this isn't the problem, then we will need to try to load the database again. Send me all of the output from when you run the ds35sqlserver_create_all_XXgb.bat

I would also suggest trying a smaller size of 1GB or even less to get things working the first time. Then increase the size to what you want to target. Using a smaller size allows you to move more quickly and resolve issues with configuration / setup faster.

Let me know how it goes.

Thanks,
Todd

[Maze999]

Hi Todd,

sorry for the confusion, i deleted that 14GB and started with a clean 24GB DB
i deleted the 24GB and tried with the suggested 1GB and still facing the same issue, Please find attached output of all scripts used
kindly let me know if any further details are needed
create-bat.txt
CreateConfig-driverexec.txt
InstallDVD.txt

[toddmuirhead]

Maze,

The load is finishing very fast. Probably too fast to have actually been successful. There are logs from the load in the ds35\sqlserverds35\load\ directories. Could you check a few and see if there are any errors? Please send me one and I can look at it if you like. For example you could send me c:\ds35\sqlserverds35\load\cust\172.x.x.x\us_cust1.log

You can run the load scripts directly to see what happens. Try running this one for example: c:\ds35\sqlserverds35\load\cust\172.x.x.x\remote_sqlserverds35_cust_load1.bat

I wonder if the bcp (bulk loader for sqlserver) that is called in these scripts is having a problem working because it is a Linux host? The logs should really show if this is the case or not.

Thanks,
Todd

[Maze999]

Hi Todd,

Log shows the below error and same is seen with manual execution of the ds35\sqlserverds35\load\cust\172.x.x.x\remote_sqlserverds35_cust_load1.bat script

Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 17 for SQL Server]Unexpected EOF encountered in BCP data-file

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 391

i cannot add the full us_cst.csv file due to size limitations, for validation i tried only with four rows to confirm the failure
attaching the us_cst.csv file which has only that four entries for reference
us_cust.csv

[toddmuirhead]

The data files that were created are Unix/Linux style files, but I think that SQL Server is still needing windows style files. Just rerun the InstallDVDstore.pl script and specify windows as the DB host type. Then just rerun the create all bat and it should work. Thanks, Todd

…

On Wed, Mar 6, 2024, 10:29 PM Maze999 ***@***.***> wrote: Hi Todd, Log shows the below error and same is seen with manual execution of the ds35\sqlserverds35\load\cust\172.x.x.x\remote_sqlserverds35_cust_load1.bat script Starting copy... SQLState = S1000, NativeError = 0 Error = [Microsoft][ODBC Driver 17 for SQL Server]Unexpected EOF encountered in BCP data-file 0 rows copied. Network packet size (bytes): 4096 Clock Time (ms.) Total : 391 i cannot add the full us_cst.csv file due to size limitations, for validation i tried only with four rows to confirm the failure attaching the us_cst.csv file which has only that four entries for reference us_cust.csv <https://github.com/dvdstore/ds35/files/14518710/us_cust.csv> — Reply to this email directly, view it on GitHub <#5 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABZ36KU3YPJSIGLWPT7O4TDYW7UK3AVCNFSM6AAAAABEGOBH5SVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSOBSGMZDMOJSGI> . You are receiving this because you commented.Message ID: ***@***.***>

[Maze999]

Thank you Todd, i was able to execute with the suggested change