0

so I am trying to do a migration from sql to db2. I am trying to write a table script in DB2 and some of the attributes(Columns) initially in SQL server was of type list (array) or like multiple values. Now, I need to write an equivalency of something like that in DB2. How do i do this ?

For example : A table showing students records has a field: ID, name, subjects So instances would be something like: 1. (1, Sarah, [math, CS, physics]) 2. (2, Max, [chem, bio, psych])

I know I could create a new table for subject and link since RDBMS, but is there anything type array or list or something I can do ?

DatCSLife
  • 1
  • 2
  • Db2 does not support arrays as SQL data types. They are supported as SQL PL data types, but you cannot create a column of an array type. – mustaccio Feb 01 '18 at 22:27
  • There is no such data type as `Array` is SQL Server, what is the actual data type in SQL Server `Varchar` or ``nVarchar`?? – M.Ali Feb 01 '18 at 22:28
  • @mustaccio: What are my other options ? – DatCSLife Feb 01 '18 at 22:29
  • @M.Ali: I don’t get to see the scripts in SQL server, struggles. I have been just given the info and asked to do them. – DatCSLife Feb 01 '18 at 22:30
  • select table right click->script table as->create table->new query editor window->copy script->select db2->paste script->execute – Hasan Jafarov Feb 01 '18 at 22:32
  • Just to rephrase, I have been given a new file with all the attributes to be put in a table and been told some of these attributes were like an array in the previous database. So from the scratch, what would u recommend? – DatCSLife Feb 01 '18 at 22:38
  • the best option in my view is to use a properly normalized data model – mustaccio Feb 01 '18 at 22:52
  • Which db2 environment are you using? In DB2 for i v7.3 you can use an aggregate function to produce a comma seperated list like above, but the data still needs to be stored in a properly normalized database. Or maybe you can store it as `JSON` in a `Varchar`. But the data will still be much easier to work with if it is stored in properly normalized tables. – jmarkmurphy Feb 02 '18 at 13:59
  • It appears that SQL Server itself does not even have multi-valued columns, but it does have functions that can build and parse comma separated lists. DB2 can do this as well if that is really what you want. Still, best practice is to normalize your tables, and remove all arrays to other tables. Everything will work better that way, and you can still present your data in lists if you want to. – jmarkmurphy Feb 02 '18 at 14:10

0 Answers0